Single Customer ID have mutiple values - show in Column level

[Varan_15]

Dear Team,

i'm new to this BI group kindly suggest this issue.

i have single customer ID contains mutiple clients hence i would show in each customer ID ->clients details as in Column level view.

example: source data

Expected Result :

please suggest.

Thanks,

Varan

[v-yueyunzh-msft]

Hi , @Varan_15 

Here are the steps you can refer to :

(1)This is my test data :

(2)We can enter the M language in "Advanced Editor" in Power Query Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIxMTUzUNJRcvRzUXD2BzEMDAyNwCJOzi5AytBUKVYHp0JDICMiMgqkzhKfOiMgIyoKpM4YyTxDqLoQ1+AQqEoDkN7IyEggCXRFbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account ID" = _t, #"Account Name" = _t, #"Client ID" = _t, #"Client Name" = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account ID", Int64.Type}, {"Account Name", type text}, {"Client ID", type text}, {"Client Name", type text}, {"Value", Int64.Type}}),
    test = Table.UnpivotOtherColumns(#"Changed Type", {"Account ID", "Account Name"}, "Attribute", "Value.1"),
    Custom1 = Table.Group(test,{"Account ID","Account Name"} , {"test",(x)=>Table.PromoteHeaders(Table.Transpose(Table.SelectColumns(x,{"Attribute","Value.1"})) ,[PromoteAllScalars=true])     }),
    #"Expanded test" = Table.ExpandTableColumn(Custom1, "test", {"Client ID", "Client Name", "Value", "Client ID_1", "Client Name_2", "Value_3", "Client ID_4", "Client Name_5", "Value_6"}, {"Client ID", "Client Name", "Value", "Client ID_1", "Client Name_2", "Value_3", "Client ID_4", "Client Name_5", "Value_6"})
in
    #"Expanded test"

(3)Then we can meet your need , the result is ass follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[danextian · Accepted solution]

Hi @Varan_15 ,

This is the  closest I cant get to what you are trying to achieve.

Please see attached PBIX for reference

[Varan_15]

Hi @danextian ,

It's working as expected thanks for your effort.