Need urgent help on convertion from Rows values to Column values in Power Bi

[Vinay07]

Hi All,

Please help on the below excel data file and screenshot to convert from Rows values to Column values in Power Bi.

I have tried so many triles, but unable to reach to solution. Please help.

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

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

POLICY_ID BUYING_YEARS_MONTHS INTEREST_RATE_CALCULATION INCREASED_LIVING_BENEFIT
8514723426 10/1/2022 0.25% 4円
8514723426 11/1/2022 0.25% 4円
8514723426 12/1/2022 0.25% 4円
8514723426 1/1/2023 0.25% 4円
8514723426 2/1/2023 0.25% 5円
8514723426 3/1/2023 0.25% 5円
8514723426 4/1/2023 0.25% 5円
8514723426 5/1/2023 0.25% 6円
8514723426 6/1/2023 0.25% 6円
8514723426 7/1/2023 0.25% 6円
8514723426 8/1/2023 0.25% 6円
8514723426 9/1/2023 0.25% 7円
8514723426 10/1/2023 0.25% 7円

[v-tangjie-msft]

Hi @Vinay07 ,

First of all, thank you qqqqqwwwweeerrr  and bhanu_gautam  for the quick reply. I will provide one other insight for your consideration:

(1) This is my test data.

(2) In PBI Desktop, click "transform data" to enter the power query editor, then click "Advanced Editor" to copy and paste the following code. You can view the steps in the Steps column on the right, and you can modify the data source by clicking the gear next to "Source".

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjA1NDE3MjYxMlPSUTI00DfUNzIwMgKyDfQMDIxMgQyTp21tSrE66EoNiVdqRLxSiEpjwiqNsKg0xarSGKfKWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"POLICY ID" = _t, BUYING_YEAR_MONTHS = _t, INTEREST_RATE_CALCULATION = _t, INCREASED_LIVING_BENEFIT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"POLICY ID", Int64.Type}, {"BUYING_YEAR_MONTHS", type date}, {"INTEREST_RATE_CALCULATION", type number}, {"INCREASED_LIVING_BENEFIT", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"POLICY ID"}, {{"Data",each Table.FromColumns(List.Transform(List.Combine(Table.ToRows(Table.SelectColumns(_,{"BUYING_YEAR_MONTHS","INTEREST_RATE_CALCULATION","INCREASED_LIVING_BENEFIT"}))),each {_}))}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"})
in
    #"Expanded Data"

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

[Vinay07]

Hi @v-tangjie-msft ,

Is it possible other Policy ID's with different dates ? 

OUTPUT:

Here are the ohter Policy ID's

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

[v-tangjie-msft · Accepted solution]

Hi @Vinay07 ,

In PBI Desktop, click "transform data" to enter the power query editor, then click "Advanced Editor" to copy and paste the following code. You can view the steps in the Steps column on the right, and you can modify the data source by clicking the gear next to "Source".

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdRBCoQwDIXhq4jrcXhJmyaeRbz/NYbpiOLYJLsHP/0WpXTbZhOqymUCLYSF8Z1vgGXSeX/dOtak2623R9ekt6RL0uuty6OXpHPS6dbrf6d+nv1OSceoFxTYsYG1ofSt1nQVHBtElX9bxVD1vE+y7olMeHQ5epQU8KsHVx+uIVwTuPhwCeGSwOzDHMKcwOTDFMIUw/2xkY7gK43gs3ow+TCFMCUwfBghjBju/9IYvtLwjtcENh+2ELYEVh/WEFYfnvf9Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"POLICY_7DIGIT BUYING_YEARS_MONTHS INTEREST_RATE_CALCULATION INCREASED_LIVING_BENEFIT" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"POLICY_7DIGIT BUYING_YEARS_MONTHS INTEREST_RATE_CALCULATION INCREASED_LIVING_BENEFIT", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type2", [PromoteAllScalars=true]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers",{{"POLICY_7DIGIT", Int64.Type}, {"BUYING_YEARS_MONTHS", type date}, {"INTEREST_RATE_CALCULATION", type number}, {"INCREASED_LIVING_BENEFIT", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type3", {"POLICY_7DIGIT"}, {{"Data", each Table.FromColumns(List.Transform(List.Combine(Table.ToRows(Table.SelectColumns(_,{"BUYING_YEARS_MONTHS","INTEREST_RATE_CALCULATION","INCREASED_LIVING_BENEFIT"}))),each {_}))}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98", "Column99", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11", "Data.Column12", "Data.Column13", "Data.Column14", "Data.Column15", "Data.Column16", "Data.Column17", "Data.Column18", "Data.Column19", "Data.Column20", "Data.Column21", "Data.Column22", "Data.Column23", "Data.Column24", "Data.Column25", "Data.Column26", "Data.Column27", "Data.Column28", "Data.Column29", "Data.Column30", "Data.Column31", "Data.Column32", "Data.Column33", "Data.Column34", "Data.Column35", "Data.Column36", "Data.Column37", "Data.Column38", "Data.Column39", "Data.Column40", "Data.Column41", "Data.Column42", "Data.Column43", "Data.Column44", "Data.Column45", "Data.Column46", "Data.Column47", "Data.Column48", "Data.Column49", "Data.Column50", "Data.Column51", "Data.Column52", "Data.Column53", "Data.Column54", "Data.Column55", "Data.Column56", "Data.Column57", "Data.Column58", "Data.Column59", "Data.Column60", "Data.Column61", "Data.Column62", "Data.Column63", "Data.Column64", "Data.Column65", "Data.Column66", "Data.Column67", "Data.Column68", "Data.Column69", "Data.Column70", "Data.Column71", "Data.Column72", "Data.Column73", "Data.Column74", "Data.Column75", "Data.Column76", "Data.Column77", "Data.Column78", "Data.Column79", "Data.Column80", "Data.Column81", "Data.Column82", "Data.Column83", "Data.Column84", "Data.Column85", "Data.Column86", "Data.Column87", "Data.Column88", "Data.Column89", "Data.Column90", "Data.Column91", "Data.Column92", "Data.Column93", "Data.Column94", "Data.Column95", "Data.Column96", "Data.Column97", "Data.Column98", "Data.Column99", "Data.Column100", "Data.Column101", "Data.Column102", "Data.Column103", "Data.Column104", "Data.Column105"})
in
    #"Expanded Data"

Best Regards,

Neeko Tang

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

[Vinay07]

Hi @v-tangjie-msft 

Is it possible to create a DAX ? Please help

[v-tangjie-msft]

Hi @Vinay07 ,

As far as I know, DAX can't achieve the results you need.Power Query is good for data cleansing.

Best Regards,

Neeko Tang

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

[qqqqqwwwweeerrr]

Hi @Vinay07 

You can check this video, Here unpivot is done using Dax (PS: this is not the exact solution you might need to modify as [er the requiremnt bu logic will work): https://youtu.be/iQWyDa6iptc?si=dvi4PxnODJI1EReL

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

Regards

[Vinay07]

At Present my data looks like below image 

Output should be like:

[bhanu_gautam]

@Vinay07 , You can use Pivot in Power query

Click on "Transform Data".
Pivot the Data:

Select the BUYING_YEARS_MONTHS column.
Click on "Transform" > "Pivot Column".
In the "Values Column" dropdown, select INCREASED_LIVING_BENEFIT.
Click "OK".
Adjust Data Types:

Ensure the new columns have the correct data types.

[Vinay07]

Is it possible in DAX ? Because I have share only one policy ID here. In original data so many ID's and BUYING_YEARS_MONTHS  date's are there.