Merging multiple rows into one row

[wthomas]

Hello, 

I'm trying to figure out a way to pull the date field into each row. In the attached I show what the data looks like and then what I'm looking to achieve. Each record is multiple rows and the date only appears on the first row of the record, but I'm looking to get the date to appear on each row. 

Any advice?

Thanks,

Whitney

[wdx223_Daniel]

NewStep=Table.Combine(Table.Group(PreviousStepName,"Column1",{"n",each let a=Table.FirstN(_,3),b=Table.LastN(_,1),c=Table.RemoveLastN(Table.Skip(_,3),1) in a&Table.ReplaceValue(c,"",a[Column3]{0},(x,y,z)=>z,{"Column3"})&b},0,(x,y)=>Byte.From(y="Company Name"))[n])

[v-jingzhang]

Hi @wthomas 

If your desired result is as the "Desired" part shows, using Power Query to do it is probably not a good option. Power Query will transform data into a table structure that each column has the same data type while your data needs to have date, text and number values in a column. Some data might be coverted into a format of another data type after loading. 

However, you can still do it with Power Query if you want. Steps are:

1. Add a conditional column

= Table.AddColumn(Source, "Date", each if [Column1] = "Company Name" then [Column3] else null)

2. Fill down this new column

3. Add a custom column

= Table.AddColumn(#"Filled Down", "Date2", each if [Column1] = "Company Name" or [Column1] = "Invoice account" or [Column1] = "Item" or Value.Is([Column1], type number) then null else [Date])

4. Remove the firstly added column and reorder columns. 

Result: (you will find that time values in Column4 are not in time format)

I created a sample with Power Query in excel, find more details from the attachment.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.