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.
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,
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.
User | Count |
---|---|
107 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |