Urgent help on convertion from Rows values to Column values in Power Bi

[Vinay07]

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 Power BI. Please help.

Here is the screenshot of date need to convert from Rows values to Column values in Power Bi visualization.

OUTPUT:

At present data looks like row wise in Power BI. Below is the example information unable to upload file in community.

Here are the records to get the output in power bi. If you direcctly past it in excel it's works. Please help

POLICY_7DIGIT BUYING_YEARS_MONTHS INTEREST_RATE_CALCULATION INCREASED_LIVING_BENEFIT
8514723 01-10-2023 0.0025 7
8514723 01-09-2023 0.0025 7
8514723 01-08-2023 0.0025 6
8514723 01-07-2023 0.0025 6
8514723 01-06-2023 0.0025 6
8514723 01-05-2023 0.0025 6
8514723 01-04-2023 0.0025 5
8514723 01-03-2023 0.0025 5
8514723 01-02-2023 0.0025 5
8514723 01-01-2023 0.0025 4
8514723 01-12-2022 0.0025 4
8514723 01-11-2022 0.0025 4
8514723 01-10-2022 0.0025 4
8303084
8009603
7867950
7801142
7758047 01-06-2018 0.0055 0
7758047 01-05-2018 0 0
7758047 01-05-2018 0 7000
7758047 01-05-2018 0.0055 0
7758047 01-04-2018 0 0
7758047 01-04-2018 0 7000
7758047 01-04-2018 0.0055 0
7758047 01-03-2018 0 0
7758047 01-03-2018 0 7000
7758047 01-03-2018 0.0055 0
7758047 01-02-2018 0 0
7758047 01-02-2018 0 7000
7758047 01-02-2018 0.0055 0
7758047 01-01-2018 0 0
7758047 01-01-2018 0 7000
7758047 01-01-2018 0.0055 0
7758047 01-12-2017 0 0
7758047 01-12-2017 0 7000
7758047 01-12-2017 0.0055 0
7758047 01-11-2017 0 0
7758047 01-11-2017 0 7000
7758047 01-11-2017 0.0055 0
7758047 01-10-2017 0 0
7758047 01-10-2017 0 7000
7758047 01-10-2017 0.0055 0
7758047 01-09-2017 0 0
7758047 01-09-2017 0 7000
7758047 01-09-2017 0.0055 0
7758047 01-08-2017 0 0
7758047 01-08-2017 0 7000
7758047 01-08-2017 0.0055 0
7758047 01-07-2017 0 0
7758047 01-07-2017 0 7000
7758047 01-07-2017 0.0055 0
7758047

[Jihwan_Kim · Accepted solution]

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]

hello @Vinay07 

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.

[Vinay07]

Hi @Irwan 

I tried with matrix visual also, but I need in DAX. Can you please try first POLICY_ID. Please help 

OUTPUT: