Forum Discussion
Merge or concatenate values in Top 2 Rows into a single Row to Promote as Headers
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 ...
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 PromotedFirstRowAsHeadersThe 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:
- 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.
- 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.
- I cannot auto-resize the columns though it does show the resize arrows.
- 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!
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"
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
PromotedFirstRowAsHeadersThe 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:
- 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.
- 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.
- I cannot auto-resize the columns though it does show the resize arrows.
- 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!