Power Query - Table transform

[ngct1112]

Hi,

Here's an interesting situation. Do you think it is possible to tansform the TableA to TableB in powerQuery? Thanks a lot.

Table A
Product	Customer1	Customer2	Customer3	Customer4
A	0.1
A		0.2
B	0.1
B		0.2
B			0.3
B				0.4
C	0.1
C		0.2

Table B
Product	Customer1	Customer2	Customer3	Customer4
A	0.1	0.2
B	0.1	0.2	0.3	0.4
C	0.1	0.2

[camargos88 · Accepted solution]

Hi @ngct1112 ,

Just paste this code on Advanced Editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlFQ0lFyLi0uyc9NLTJEYhshsY2R2CZKsTrRSo5AEQM9kHoFOIaJK4DljFDEnXCod8KjXgEuZ4xVHCIHcY8zDvOdsZkfCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Replaced Value" = Table.ReplaceValue(#"Promoted Headers",".",",",Replacer.ReplaceText,{"Customer1", "Customer2", "Customer3", "Customer4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Customer1", type number}, {"Customer2", type number}, {"Customer3", type number}, {"Customer4", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Product "}, {{"Customer1", each List.Sum([Customer1]), type number}, {"Customer2", each List.Sum([Customer2]), type number}, {"Customer3", each List.Sum([Customer3]), type number}, {"Customer4", each List.Sum([Customer4]), type number}})
in
#"Grouped Rows"

Ricardo

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product ", type text}, {"Customer1", type number}, {"Customer2", type number}, {"Customer3", type number}, {"Customer4", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product "}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product ", type text}, {"Customer1", type number}, {"Customer2", type number}, {"Customer3", type number}, {"Customer4", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product "}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Hope this helps.

[ngct1112]

@Ashish_Mathur it work prefectly. Great thanks!

[Ashish_Mathur]

You are welcome.

[Greg_Deckler]

Yes, use Group By

[ngct1112]

@Greg_Deckler Could you give a little advice.

When I groupby(By all rows), then spread it out, it still remain the same structure.

Do you know any way to make it happen? Thanks!

[Greg_Deckler · Accepted solution]

This is the Group By version:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlFQ0lFyLi0uyc9NLTJEYhshsY2R2CZKsTrRSo5AEQM9kHoFOIaJK4DljFDEnXCod8KjXgEuZ4xVHCIHcY8zDvOdsZkfCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product ", type text}, {"Customer1", type number}, {"Customer2", type number}, {"Customer3", type number}, {"Customer4", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Product "}, {{"Customer1", each List.Max([Customer1]), type number}, {"Customer2", each List.Max([Customer2]), type number}, {"Customer3", each List.Max([Customer3]), type number}, {"Customer4", each List.Max([Customer4]), type number}})
in
    #"Grouped Rows"

[camargos88 · Accepted solution]

Hi @ngct1112 ,

Just paste this code on Advanced Editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlFQ0lFyLi0uyc9NLTJEYhshsY2R2CZKsTrRSo5AEQM9kHoFOIaJK4DljFDEnXCod8KjXgEuZ4xVHCIHcY8zDvOdsZkfCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Replaced Value" = Table.ReplaceValue(#"Promoted Headers",".",",",Replacer.ReplaceText,{"Customer1", "Customer2", "Customer3", "Customer4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Customer1", type number}, {"Customer2", type number}, {"Customer3", type number}, {"Customer4", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Product "}, {{"Customer1", each List.Sum([Customer1]), type number}, {"Customer2", each List.Sum([Customer2]), type number}, {"Customer3", each List.Sum([Customer3]), type number}, {"Customer4", each List.Sum([Customer4]), type number}})
in
#"Grouped Rows"

Ricardo

[ngct1112]

@camargos88 this is a smart and qucik way to do the transformance. this solve my problem. Great Thanks!! 

[camargos88]

@ngct1112 ,

I glad it helped you.

If possible, please mark this as an solution and kudos.

Thanks,

Ricardo