Forum Discussion
Anonymous
2 years agoNot applicable
Urgent help on convertion from Rows values to Column values in Power Bi
Hi All, Please help on the below sample data and attached screenshot to convert from Rows values to Column values in Power Bi. I have tried so many triles, but unable to reach to solution in ...
- 2 years ago
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the atthached pbix file.
One of ways is to transform data like below in power query editor.
let Source = data_source, #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"POLICY_7DIGIT"}, "Attribute", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"POLICY_7DIGIT"}, {{"data", each _, type table [POLICY_7DIGIT=nullable number, Attribute=text, Value=any]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Index", each Table.AddIndexColumn([data],"Index_Per_Policy",0,1)), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Index"}), #"Expanded Index" = Table.ExpandTableColumn(#"Removed Other Columns", "Index", {"POLICY_7DIGIT", "Attribute", "Value", "Index_Per_Policy"}, {"POLICY_7DIGIT", "Attribute", "Value", "Index_Per_Policy"}), #"Integer-Divided Column" = Table.TransformColumns(#"Expanded Index", {{"Index_Per_Policy", each Number.IntegerDivide(_, 3), Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Attribute]), "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column","N/A",null,Replacer.ReplaceValue,{"BUYING_YEARS_MONTHS", "INTEREST_RATE_CALCULATION", "INCREASED_LIVING_BENEFIT"}), #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"BUYING_YEARS_MONTHS", type date}, {"INTEREST_RATE_CALCULATION", type number}, {"INCREASED_LIVING_BENEFIT", Int64.Type}}) in #"Changed Type"
Irwan
Super User
2 years agohello Anonymous
you surely can do this with DAX, but there is a drawback.
since you want to put it horizontally, you need to create multiple column so you need to ADDCOLUMN for every value. and if there is another input for same POLICY_ID, you need ADDCOLUMN manually again to create more columns.
there is a way to make your need without much change is by using matrix visual.
Hope this will help you.
Thank you.
- Anonymous2 years agoNot applicable
Hi Irwan
I tried with matrix visual also, but I need in DAX. Can you please try first POLICY_ID. Please help
OUTPUT: