topic Re: Need urgent help on convertion from Rows values to Column values in Power Bi in DAX Commands and Tips

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

Anonymous — Mon, 12 Aug 2024 08:27:06 GMT

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円

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

bhanu_gautam — Mon, 12 Aug 2024 09:16:45 GMT

Anonymous , 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.

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

Anonymous — Mon, 12 Aug 2024 10:09:17 GMT

At Present my data looks like below image

Output should be like:

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

Anonymous — Mon, 12 Aug 2024 10:17:10 GMT

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.

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

qqqqqwwwweeerrr — Mon, 12 Aug 2024 12:52:31 GMT

Hi Anonymous

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

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

Anonymous — Wed, 14 Aug 2024 06:26:06 GMT

Hi Anonymous ,

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.

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

Anonymous — Wed, 14 Aug 2024 07:31:46 GMT

Hi Anonymous ,

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

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

Anonymous — Thu, 15 Aug 2024 01:51:43 GMT

Hi Anonymous ,

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.

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

Anonymous — Fri, 16 Aug 2024 06:12:51 GMT

Hi Anonymous

Is it possible to create a DAX ? Please help

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

Anonymous — Fri, 16 Aug 2024 07:29:27 GMT

Hi Anonymous ,

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.