Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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:
Can someone address these questions inorder to have a concise, dynamic and efficient solution? I am here to learn!
This is really helpful. thank you
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 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:
Can someone address these questions inorder to have a concise, dynamic and efficient solution? I am here to learn!
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.