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]

your source table is not 1NF table and imo not representative
but

Column1	Column2	Column3	Column4	Column5		Column1	Column2	Column3	Column4	Column5	Custom
Name 1						Name 1
Day	Date	Something	Something else	Something more		Day	Date	Something	Something else	Something more	Staff Name
Mon						Mon					Name 1
Tue						Tue					Name 1
Sun						Sun					Name 1
Name 2						Name 2
Day						Day
Mon						Mon					Name 2
Thu						Thu					Name 2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IF = Table.AddColumn(Source, "FD", each if not Text.Contains([Column1], "Name") or [Column1] = "Day" then null else [Column1]),
    FD = Table.FillDown(IF,{"FD"}),
    IF2 = Table.AddColumn(FD, "Staff", each if [Column1] = "Day" or Text.Contains([Column1], "Name") then null else [FD]),
    Replace = Table.ReplaceErrorValues(IF2, {{"Staff", null}}),
    IF3 = Table.AddColumn(Replace, "Custom", each if [Column1] = "Day" and [Column2] <> null then "Staff Name" else [Staff]),
    TSC = Table.SelectColumns(IF3,{"Column1", "Column2", "Column3", "Column4", "Column5", "Custom"})
in
    TSC

 

[Willforth]

your source table is not 1NF table and imo not representative
but

Column1	Column2	Column3	Column4	Column5		Column1	Column2	Column3	Column4	Column5	Custom
Name 1						Name 1
Day	Date	Something	Something else	Something more		Day	Date	Something	Something else	Something more	Staff Name
Mon						Mon					Name 1
Tue						Tue					Name 1
Sun						Sun					Name 1
Name 2						Name 2
Day						Day
Mon						Mon					Name 2
Thu						Thu					Name 2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IF = Table.AddColumn(Source, "FD", each if not Text.Contains([Column1], "Name") or [Column1] = "Day" then null else [Column1]),
    FD = Table.FillDown(IF,{"FD"}),
    IF2 = Table.AddColumn(FD, "Staff", each if [Column1] = "Day" or Text.Contains([Column1], "Name") then null else [FD]),
    Replace = Table.ReplaceErrorValues(IF2, {{"Staff", null}}),
    IF3 = Table.AddColumn(Replace, "Custom", each if [Column1] = "Day" and [Column2] <> null then "Staff Name" else [Staff]),
    TSC = Table.SelectColumns(IF3,{"Column1", "Column2", "Column3", "Column4", "Column5", "Custom"})
in
    TSC

Thank you so much for this. NEVER IN A MILLION YEARS would I have got this. All I need do now is figure out where to put this code! I told you I was a Newbie! Once again. Many thanks

 

[sandy666]

will be much easier if you update your profile (Account details) about Excel version and OS, don't forget to scroll down and hit Save

 

[sandy666]

ok, thanks for update
so try

• 1. select your whole range then use Ctrl+T to create Excel Table (with My Table has headers unchecked)
• 2. put cursor somewhere on this table and from the ribbon Data tab use From Table, it will open Power Query Editor
• 3. Go to Advanced Editor and replace code there with code copied from the post
• 4. make sure the name of the table in the code is the same as your source table (here is Table1, change in the code if necessary)
• 5. OK then Close&Load

hope I haven't forgotten something

 

[Willforth]

Hi,

I think I am NOT providing you with the full information within the previous table I posted. I got your code to run fine (thanks for the instructions on how to do that) However, you can see from the attached that it posts "Staff" not the actual names ....in this case Steve Smith and Claire Williams in the rows that contains days in the first column. I hope the attached image is OK. This is taken from a live table that I have altered slightly to preserve confidentiality but all the columns are there and accurate

What is wrong?

Can you help....again!!

 

[sandy666]

your image from the post#6 is quite different then your example from post#1
if you will use example from post#1 it should work. Solution is tailored to the example.
I suggest to post a link to the shared excel file with representative source example and expected result. Use a free service like onedrive, googledrive or any similar
or
use XL2BB to post these two things

 

[Willforth]

Thanks for all your help so far. I downloaded and installed XL2BB and below is the data I have pasted from th etable. I have changed names etc. for confidentiality but all else is the same. The second table shows the desired output.

The table has already been edited via Power Query to get rid of some of the rows. 2 columns "Timesheet" and "Comments" are a split from a single column made in Power Query. Not sure this is relevant just wanted to give full disclosure.

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	Column from Code
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	Staff Name
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	null
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	null
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	null
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	null
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	null
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	null
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	Staff Name
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	null
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	null

DESIRED OUTCOME

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	17: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	16: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	08: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	08: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	15: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	23:59		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	08: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	08: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	08: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	08:00	1	Client 11	TS 34142		8.21			13.01	CS11	CS1-WK		11	90.31	143.11	52.8	Claire Williams

 

[sandy666]

ok. I'll try do that in my spare time

 

[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	44151	0.375	0.708333333		Client 1	TS 34420		8.21			13.5	CS1	CS1-WK		8	65.68	108	42.32	Steve Smith
Tue	44152	0.375	0.666666667		Client 2	TS 34423		8.21			13.5	CS2	CS2-WE		7	57.47	94.5	37.03	Steve Smith
Tue	44152	0.833333333	0.333333333		Client 3	TS 34415		8.21			13.26	CS3	CS1-WK		12	98.52	159.12	60.6	Steve Smith
Fri	44155	0.833333333	0.333333333		Client 4	TS 34413		8.21			13.26	CS4	CS2-WE		12	98.52	159.12	60.6	Steve Smith
Sun	44157	0.375	0.625		Client 5	TS 34423		9	Sunday Basic	SunB	14.5	CS5	CS1-WK		6	54	87	33	Steve Smith
Sun	44157	0.833333333	0.999305556		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	44157	0.999305556	0.333333333	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	44151	0.833333333	0.333333333	1	Client 9	TS 34142		8.21			13.01	CS9	CS1-WK		11	90.31	143.11	52.8	Claire Williams
Thu	44154	0.833333333	0.333333333	1	Client 10	TS 34142		8.21			13.01	CS10	CS2-WE		11	90.31	143.11	52.8	Claire Williams
Fri	44155	0.833333333	0.333333333	1	Client 11	TS 34142		8.21			13.01	CS11	CS1-WK		11	90.31	143.11	52.8	Claire Williams

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FD = Table.FillDown(Table.AddColumn(Source, "Custom", each if [Staff] <> "" and [Date] = null then [Staff] else null),{"Custom"}),
    Result = Table.RemoveColumns(Table.AddColumn(FD, "Desired Output", each if [Staff] <> "" and [Date] = null then null else [Custom]),{"Custom"})
in
    Result