Add a new column in Excel repeating data in a single Cell in a column that also has data that I do not want in the column using Power Query

[Willforth]

I apologise for the confused title but I do not know how to succinctly describe my request for help. I thought it best to create a table to show what I mean (see below).

The text in green shows the name I already have in my table and it's location. I want to use Power Query to add a new column (shown in red) and insert the green text in all the rows where there is a day of the week showing in Column A (See yellow text for where I want the copied data inserted).

The next block of data starts with a new name (see second instance of green text) and I want that placed in the next set of rows where there is a day of the week showing in Column A (See yellow text for where I want the copied data inserted). I have around 100 names that need to be inserted in this manner so it is a long list.

I have tried Inserting a column using a conditional statement but frankly, all I am getting is frustration.

Please can you help a Newbie?

Name 1
Day	Date	Something	Something else	Something more	Staff Name
Mon					Name 1
Tue					Name 1
Sun					Name 1
Name 2
Day
Mon					Name 2
Thu					Name 2

 

[sandy666]

Staff	Date	Start	Finish	Break	Client	Timesheet	Comment	Pay-Hr	Pay-Name	Pay-Code	Sales-Hr	Sales-Name	Sales-Code	Multiplier	Hours	Pay	Sales	Margin	Desired Output
Steve Smith		56 hours	467.65
Mon	16/11/2020	09:00:00	17:00:00		Client 1	TS 34420		8.21			13.5	CS1	CS1-WK		8	65.68	108	42.32	Steve Smith
Tue	17/11/2020	09:00:00	16:00:00		Client 2	TS 34423		8.21			13.5	CS2	CS2-WE		7	57.47	94.5	37.03	Steve Smith
Tue	17/11/2020	20:00:00	08:00:00		Client 3	TS 34415		8.21			13.26	CS3	CS1-WK		12	98.52	159.12	60.6	Steve Smith
Fri	20/11/2020	20:00:00	08:00:00		Client 4	TS 34413		8.21			13.26	CS4	CS2-WE		12	98.52	159.12	60.6	Steve Smith
Sun	22/11/2020	09:00:00	15:00:00		Client 5	TS 34423		9	Sunday Basic	SunB	14.5	CS5	CS1-WK		6	54	87	33	Steve Smith
Sun	22/11/2020	20:00:00	23:59:00		Client 6	TS 31777	56HRS508	9	Sunday Basic	SunB	14.01	CS6	CS2-WE		3.98	35.85	55.81	19.96	Steve Smith
Sun	22/11/2020	23:59:00	08:00:00	1	Client 7	TS 31777	56HRS508	8.21			14.01	CS7	CS1-WK		7.02	57.61	98.3	40.7	Steve Smith
Claire Williams		44 hours	364.39		Client 8
Mon	16/11/2020	20:00:00	08:00:00	1	Client 9	TS 34142		8.21			13.01	CS9	CS1-WK		11	90.31	143.11	52.8	Claire Williams
Thu	19/11/2020	20:00:00	08:00:00	1	Client 10	TS 34142		8.21			13.01	CS10	CS2-WE		11	90.31	143.11	52.8	Claire Williams
Fri	20/11/2020	20:00:00	08:00:00	1	Client 11	TS 34142		8.21			13.01	CS11	CS1-WK		11	90.31	143.11	52.8	Claire Williams

you'll need to use appropriate format cells for Date, Start and Finish columns because as I said before your table is not a proper 1NF type

 

[Willforth]

Good Morning. I ran the code you kindly supplied but all I got was "Error" on every row line in the new column that was added via this script

I also changed the column types as you suggested

 

[sandy666]

test M on your source from here

Staff	Date	Start	Finish	Break	Client	Timesheet	Comment	Pay-Hr	Pay-Name	Pay-Code	Sales-Hr	Sales-Name	Sales-Code	Multiplier	Hours	Pay	Sales	Margin
Steve Smith		56 hours	467.65
Mon	16/11/2020	09:00	17:00		Client 1	TS 34420		8.21			13.5	CS1	CS1-WK		8	65.68	108	42.32
Tue	17/11/2020	09:00	16:00		Client 2	TS 34423		8.21			13.5	CS2	CS2-WE		7	57.47	94.5	37.03
Tue	17/11/2020	20:00	08:00		Client 3	TS 34415		8.21			13.26	CS3	CS1-WK		12	98.52	159.12	60.6
Fri	20/11/2020	20:00	08:00		Client 4	TS 34413		8.21			13.26	CS4	CS2-WE		12	98.52	159.12	60.6
Sun	22/11/2020	09:00	15:00		Client 5	TS 34423		9	Sunday Basic	SunB	14.5	CS5	CS1-WK		6	54	87	33
Sun	22/11/2020	20:00	23:59		Client 6	TS 31777	56HRS508	9	Sunday Basic	SunB	14.01	CS6	CS2-WE		3.98	35.85	55.81	19.96
Sun	22/11/2020	23:59	08:00	1	Client 7	TS 31777	56HRS508	8.21			14.01	CS7	CS1-WK		7.02	57.61	98.3	40.7
Claire Williams		44 hours	364.39		Client 8
Mon	16/11/2020	20:00	08:00	1	Client 9	TS 34142		8.21			13.01	CS9	CS1-WK		11	90.31	143.11	52.8
Thu	19/11/2020	20:00	08:00	1	Client 10	TS 34142		8.21			13.01	CS10	CS2-WE		11	90.31	143.11	52.8
Fri	20/11/2020	20:00	08:00	1	Client 11	TS 34142		8.21			13.01	CS11	CS1-WK		11	90.31	143.11	52.8

use this to copy data

 

[Willforth]

I tried it and it Works!! So clearly there has to be something different in my data. I will investigate but any suggestions welcome

 

[sandy666]

I don't know your original data so I can't help
the best way will be suggestion from third line of post#7

remember that Power Query is case sensitive so ABC is not the same as abc
check data against unwanted spaces in headers

 

[Willforth]

Thank you for your patience. Here is a link to the file

Sandy666

Folder

1drv.ms

 

[sandy666]

the column names are completely different then in your example which was not representative

Expression.Error: The field 'Staff' of the record wasn't found.
Details:
Column1=Alton Zulu
Column2=
Column3=56 hours
Column4=467.65
Column5=
Column6=
Column7=
Column8=
Column9=
Column10=
Column11=
Column12=
Column13=
Column14=

and so on...

 

[Willforth]

But what about the Column headers? Does that not get picked up in the formula?

 

[sandy666]

No, column names are defined in the Source step taken from the source table. You cannot use free-style with Power Query

I think you should read this

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

 

[sandy666]

try

// Query1
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    FD = Table.FillDown(Table.AddColumn(Source, "Custom", each if [Column1] <> "" and [Column2] = null then [Column1] else null),{"Custom"}),
    Result = Table.RemoveColumns(Table.AddColumn(FD, "Staff Name", each if [Column1] = [Custom] and [Column2] = null or [Column2] = "Date" then null else [Custom]),{"Custom"})
in
    Result

with source data you posted in excel file

see this

s666-PQ-Billing.xlsx

Microsoft Excel Workbook

1drv.ms