Formatting a table column into multiple lines

[Anonymous]

Im looking to either do this in DAX or Power query to create a new table where some of the colums need to become a row and duplicate some of the information in the row against the column.

Maybe easier to show in the example

Original Data
Name	Status	Rating	Score	ID1	ID2	ID3	ID4	ID5	Date
ABC	Expired	Low	130	23452	12123	1231	31224		1/01/2020
EFG	Accepted	High	15	4567					1/01/2020
HIJ	Accepted	Medium	120	678	908				1/01/2020
KLM	Accepted	High	110						1/01/2020
Outcome
No	Name	Status	Rating	Score	Date
23452	ABC	Expired	Low	130	1/01/2020
12123	ABC	Expired	Low	130	1/01/2020
1231	ABC	Expired	Low	130	1/01/2020
31224	ABC	Expired	Low	130	1/01/2020
4567	EFG	Accepted	High	15	2/01/2020
678	HIJ	Accepted	Medium	120	3/01/2020
908	HIJ	Accepted	Medium	120	4/01/2020

[SamInogic · Accepted solution]

Hi @Anonymous ,

You can below steps to achieve the required outcome,

1. Open your table in Power Query Editor as shown in below screenshot,
   
   
   
2. Select the columns >> ID1, ID2, ID3, ID4 >> click on transform menu from menu bar >> Click on Unpivot Selected Columns option. Please refer to the below screenshot for the same.
   
   
   
   
3. This will create two new columns as Attribute and Value. Rename the Value Column as “No”.
   
   
   
4. Now Close and Apply the changes and add the table on the report with required columns as shown in the below screenshot.
   
   

Thanks!

Inogic Professional Services Division

Power Platform and Microsoft Dynamics 365 CRM Development – All under one roof!

Drop an email at crm@inogic.com

Services:  http://www.inogic.com/services/

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/