Arranging data

[Anonymous]

Good morning guys,

I have a table which shown in below and need to convert it in well manner.

Anyone have idea how may i do it.

Thank you guys Cheers

Oli

[ImkeF · Accepted solution]

Hi @Anonymous ,

provided a stable structure like in your sample, you can do it like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KSS1KTbFS0lEyNNA3MNY3MjAyBHJAKFYnWsk5JzM1rwSsyi8fpErZyNTE3MwCyAopSswrhgiamhsYWJqDNZBpoCUWAy2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"All", each Table.FromRows( {List.Select( List.Union( Table.ToColumns( Table.SelectColumns(_,{"Column2", "Column4"}) ) ), each _ <> null)})}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"})
in
    #"Expanded All"

Next time, please provide sample data in a usable form, so that we don't have to type that in manually:

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

[amitchandak]

@Anonymous , I do no think, this is an ideal format of data for any BI tool. Better to correct the source.

That will be a lot of effort to get this rolling.

@ImkeF , any solution for this ?