Please help to transform data which is all in one cloumn

[Grolmo]

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

[PhilipTreacy · Accepted 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

[Grolmo]

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!

[Pragati11]

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

[Grolmo]

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,