Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello All,
this is my firt post since I am just starting with PowerBI and would appreciate some ideads on how I can solve my issue.
I get a data file where all data, including header and data points are stored in a single column. I would like to transform this into a proper table.
I managed to pull out all the headings from the file but now I am running into issues with the data itself.
The Data is structured like this:
00D
Value X1
Value X2
Value X3
00D
Value Y1
Value Y2
Value Y3
00D
Value Z1
Value Z2
Value Z3
and I would to the show like this:
Value X1 | Value X2 | Value X3 |
Value Y1 | Value Y2 | Value Y3 |
Value Z1 | Value Z2 | Value Z3 |
Can anybody hint me in the right direction on how i can approach this? I am juststarting out so any support would be much appreciated.
Thanks a lot
Solved! Go to Solution.
Hi @Grolmo
Download this PBIX file with a working solution that includes this Power Query/M code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBwUYrViVYKS8wpTVWIMETmGCFzjMEcVOWRyMojkZVHYlMehaw8Cll5FFB5LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "00D")),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundUp([Index]/3)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom"}, {{"Count", each _, type table [Column1=nullable text, Custom=number]}}),
#"Removed Columns2" = Table.RemoveColumns(#"Grouped Rows",{"Custom"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns2", "Custom.1", each Table.Transpose([Count])),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Count"}),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns1", "Custom.1", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}),
#"Removed Alternate Rows" = Table.AlternateRows(#"Expanded Custom.1",1,1,1)
in
#"Removed Alternate Rows"
Starting with this
Ending up with this
Regards
Phil
Proud to be a Super User!
Hi @Grolmo
Download this PBIX file with a working solution that includes this Power Query/M code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBwUYrViVYKS8wpTVWIMETmGCFzjMEcVOWRyMojkZVHYlMehaw8Cll5FFB5LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "00D")),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundUp([Index]/3)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom"}, {{"Count", each _, type table [Column1=nullable text, Custom=number]}}),
#"Removed Columns2" = Table.RemoveColumns(#"Grouped Rows",{"Custom"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns2", "Custom.1", each Table.Transpose([Count])),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Count"}),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns1", "Custom.1", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}),
#"Removed Alternate Rows" = Table.AlternateRows(#"Expanded Custom.1",1,1,1)
in
#"Removed Alternate Rows"
Starting with this
Ending up with this
Regards
Phil
Proud to be a Super User!
Dear @PhilipTreacy
Thanks a lot for this suggestion it works great in my situation. And adding the PBIX file help a lot so i could reproduce the steps!
Hi @Grolmo ,
Require little more details here.
Your input is in single column. Right?
Your output is also in a single column or 3 different columns?
You need to add more here what is the requirement.
Thanks,
Pragati
Hi @Pragati11 ,
thanks a lot for the quick reply. Yes, that source data is in a single column and I want the output in three different columns. The 00D always shows that a new row should start and is not part of the data I want to manipulate.
Thanks a lot,
User | Count |
---|---|
98 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
58 |