Merge or concatenate values in Top 2 Rows into a single Row to Promote as Headers

[Anonymous]

I have a table data imported from csv's or xlsx file that looks like this:

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10
null	null	null	null	null	null	null	Units	Units	%Reach
Mkts	Dept	SCat	Cat	Seg	Brand	Upc	4 W/E 10/06/17	4 W/E 11/03/17	4 W/E 12/01/17
ABC	someDept1	someCat1	FOOD	VEGGIES	XWAR	3939493	2321.11	6883.43	49.13
ABC	someDept1	someCat1	FOOD	VEGGIES	XWAR	5946942	422.32	222.64	91.84
ABC	someDept1	someCat1	FOOD	VEGGIES	XWAR	4938843	2543.34	null	null
CDE	someDept2	someCat2	BEV	NON-VEG	SAG	0549403	null	null	2
DEF	someDept3	someCat3	UTIL	DAIRY	MUG	04032850	2	null	null

The Columns 1 to 7 indicate Dimension columns (see Row 2). This may be variable between different datasets and one may not know their names. Similarly, the Columns 8 to 10 indicate Fact columns (see Row 1). This may also be variable between different datasets and one may not know their names. The only way is that there are "null" values in the 1st row till the start of the Fact Columns.

I want to merge the values in these top 2 rows into a single row using "~" as a Delimiter, the single row, which i can then promote as Header. The final table should look like this:

Mkts	Dept	SCat	Cat	Seg	Brand	Upc	Units~4 W/E 10/06/17	Units~4 W/E 11/03/17	%Reach~4 W/E 12/01/17
ABC	someDept1	someCat1	FOOD	VEGGIES	XWAR	39393493	2321.11	6883.43	49.13
ABC	someDept1	someCat1	FOOD	VEGGIES	XWAR	59493942	422.32	222.64	91.84
ABC	someDept1	someCat1	FOOD	VEGGIES	XWAR	49382843	2543.34	null	null
CDE	someDept2	someCat2	BEV	NON-VEG	SAG	05490403	null	null	2
DEF	someDept3	someCat3	UTIL	DAIRY	MUG	04032850	2	null	null

Note:

1. I am using "~" as a Delimiter for the next part of the query where i will be pivoting the Facts columns, splitting and removing the dates. 
2. I cannot TRANSPOSE Rows to Columns as the number of rows exceed 16,384 and there is a limitation on TRANSPOSE in Power Query that it can only handle 16,384 columns.

[Tom_Morris]

Thank you - your script worked perfectly for what I needed

[Anonymous]

This is really helpful. thank you

[v-frfei-msft]

Hi @Anonymous ,

To remove top 1 row and Promoted Headers. M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZFPS8NAEMW/Sgl4q5vdnUncPebPtgRsA4lpLaGH0AYVayy2fn9nopBYT/by3uNt8puBqWuv+zwcvOk/rOpezqeR3xRts3v2ttPaW7z2Tdoez2Rl0rB9a9k+kcYfTbfnf487UpysfTdR0pehr+6GQvkSfhXal4oLnhHFCT2c3t9aHqN+Mg3hOMvzlGzl5vPMlZQe11FBBhYsWqCkQSuh+NvQGBDIHVqh4Hp4YDG0qBmktQAOmkKIFKwSBq9H087G9DvqAEEAXlyDwUnqRmA9gDnGbkW6zJe3xOYzRKwBWpTw57K656VuNuLBwONYPWT3fOEoKzbki4pxxNImkD3hYr/tFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Mkts", type text}, {"Dept", type text}, {"SCat", type text}, {"Cat", type text}, {"Seg", type text}, {"Brand", type text}, {"Upc", Int64.Type}, {"4 W/E 10/06/17", type number}, {"4 W/E 11/03/17", type number}, {"4 W/E 12/01/17", type number}})
in
    #"Changed Type1"

[Anonymous · Accepted solution]

Hi @v-frfei-msft, @Nathaniel_C ,

Thank you all for your valuable inputs. 🙂

Last night after posting, i gave a last try and came up with this solution. Though it is long, it does serve my purpose.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZLdSsNAEIVfpQS8q5vdmUncvczPtgRsI41plZCLoEFFrGL7/jgTi0nBq0I453DIfrNk0jRBMP/nqfdvx8PErzZ99/QatPMmWL0PTd5/HdmqrBP71ap/YU2/u/2znL3LWGm2C/3M6FDHobkZCxNqPCsg1EYKmZGkcvTw+dHLGHPKPETioixztq1fLgtfcXrYJRs2dOjIISdAMMrIu7G1qEg6csrg5fDIUewIBASgUAJwiImDM8rS5Wi+s7XDHSEiVEinLQgwy/0ECCNQYuq3rOtyfc1M+fyJqI7Ikca/VcIAyv1iAsIRJLG+L25lpUmxeWRf1QOHIWAjPf4WbfsD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t]),
    
    // take first 2 rows and transform them
    First2Rows = Table.FirstN(Source, 2),
    TransposeRows2Columns = Table.Transpose(First2Rows),
    AddCustomColumn = Table.AddColumn(TransposeRows2Columns, "Custom", each if [Column1] = "" then [Column2] else [Column1]&"~"&[Column2]),
    RemoveFirst2Columns = Table.RemoveColumns(AddCustomColumn,{"Column1", "Column2"}),
    TransposeColumn2Rows = Table.Transpose(RemoveFirst2Columns),
    SourceWithout2FirstRows = Table.RemoveFirstN(Source, 2),
    CombineRows2Source = Table.Combine({TransposeColumn2Rows,SourceWithout2FirstRows}),
    PromotedFirstRowAsHeaders = Table.PromoteHeaders(CombineRows2Source, [PromoteAllScalars=true])
in
    PromotedFirstRowAsHeaders

The Result is how i expect it:

Note: I am only transposing the 1st 2 rows as the dataset is huge and it will exceed 16384 columns if i transpose all. Besides, it is also going to hog my memory.

A few observations of PowerBI:

1. Can the steps be shortened or is there a more efficient way of doing this? I have more steps following these steps to do further transformations.
2. Right now, i am considering only 1st 2 rows. But what if one of the data extracts has more number of null rows on top and some cells may contain text in it? I think the code would fail to determine the Dimensions and Facts columns correctly.
3. I cannot auto-resize the columns though it does show the resize arrows.
4. I am unable to enter null values in cells in Power BI > Enter Data.

Can someone address these questions inorder to have a concise, dynamic and efficient solution? I am here to learn!

[Nathaniel_C]

Hi @Anonymous ,

Using Group by this might work.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel