Power Query Transformation

[vw_golf_mk3]

Hi peeps,

Can anyone please help in solving a little tranformation challenge that I am facing as shown below by using Power BI/Query?

 Thanks,

[Thejeswar · Accepted solution]

Try Using the following M-Query for this purpose

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWBsJLgrGQwywguBmKlgFnGEFYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, roi = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"roi", type text}}),
    Partition = Table.Group(#"Changed Type", {"id"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"roi", "Index"}, {"roi", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Custom", each if [Index] = 1 then "roi1" else if [Index] = 2 then "roi2" else "roi3")
in
    #"Added Custom"

The Output is as shown below

[Iamnvt · Accepted solution]

you can try the following as well.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each "roi"),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"id", type text}, {"roi", type text}, {"Custom", type text}}),
    Group_index = Table.Group(#"Changed Type", {"id"}, {{"index", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded index" = Table.ExpandTableColumn(Group_index, "index", {"roi", "Custom", "Index"}, {"roi", "Custom", "Index.1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded index",{{"roi", type text}, {"Custom", type text}, {"Index.1", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each [Custom]&[Index.1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Index.1"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom.1]), "Custom.1", "roi")
in
    #"Pivoted Column"

Br, T

[v-yulgu-msft · Accepted solution]

Hi @vw_golf_mk3,

Please refer to Iamnvt's solution which can be achieved easily by GUI.

Group data based on id column.

Then, make a little modification to the highlighted code. And expand the "all data" field.

Choose "Index" column then pivot table.

Best regards,

Yuliana Gu

[Thejeswar · Accepted solution]

Try Using the following M-Query for this purpose

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWBsJLgrGQwywguBmKlgFnGEFYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, roi = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"roi", type text}}),
    Partition = Table.Group(#"Changed Type", {"id"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"roi", "Index"}, {"roi", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Custom", each if [Index] = 1 then "roi1" else if [Index] = 2 then "roi2" else "roi3")
in
    #"Added Custom"

The Output is as shown below

[vw_golf_mk3]

@Thejeswar That's great.

Is it possible to achieve this via GUI (clicking)?

Cheers,

[Iamnvt · Accepted solution]

you can try the following as well.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each "roi"),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"id", type text}, {"roi", type text}, {"Custom", type text}}),
    Group_index = Table.Group(#"Changed Type", {"id"}, {{"index", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded index" = Table.ExpandTableColumn(Group_index, "index", {"roi", "Custom", "Index"}, {"roi", "Custom", "Index.1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded index",{{"roi", type text}, {"Custom", type text}, {"Index.1", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each [Custom]&[Index.1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Index.1"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom.1]), "Custom.1", "roi")
in
    #"Pivoted Column"

Br, T