Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 ...
  • Jihwan_Kim's avatar
    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"