Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Hi @Varan_15 ,
This is the closest I cant get to what you are trying to achieve.
Please see attached PBIX for reference
Proud to be a Super User!
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
Hi @Varan_15 ,
This is the closest I cant get to what you are trying to achieve.
Please see attached PBIX for reference
Proud to be a Super User!