Solved: Re: Pivot columns to rows in power query - I have ...

DSR · ‎02-25-2025

Please help me Pivot the following table from Yr (Y00…Yr10) Vertical to Yr Horizontal. In Power Query

Your Detail explanation is very much appreciated

The table has the following Columns:

Case.CC.EEName

PeriodNo

Yr

NoEE.inPrvPrd

Yr Move Out

Index.inGrp

NoEE

Tenure

EE.Refill

EENude

The Columns Yr is listed Verically. I want to turn the Yr HORIZONTALLY

TableBefore

EENude	Yr	EE.Refill	NoEE	PeriodNo	NoEE.inPrvPrd	Yr Move Out	Index.inGrp	Tenure
	Yr00		0	0	0	2	0
A.01	Yr01	1	1	1	0	2	1	1
A.01	Yr02	1	1	2	1	2	2	2
A.02	Yr03	2	1	3	1	2	3	1
	Yr04		0	4	1	2	4
A.03	Yr05	3	1	5	0	2	5	1
	Yr06		0	6	1	2	6
	Yr07		0	7	0	2	7
	Yr08		0	8	0	2	8
	Yr09		0	9	0	2	9
	Yr10		0	10	0	2	10
	Yr00		0	0	0	2	0
B.01	Yr01	1	1	1	0	2	1	1
B.01	Yr02	1	1	2	1	2	2	2
B.02	Yr03	2	1	3	1	2	3	1
	Yr04		0	4	1	2	4
B.03	Yr05	3	1	5	0	2	5	1
B.03	Yr06	3	1	6	1	2	6	2
B.04	Yr07	4	1	7	1	2	7	1
B.04	Yr08	4	1	8	1	2	8	2
B.05	Yr09	5	1	9	1	2	9	1
B.05	Yr10	5	1	10	1	2	10	2
	Yr00		0	0	0	5	0
	Yr01		0	1	0	5	1
	Yr02		0	2	0	5	2
C.01	Yr03	1	1	3	0	5	3	1
C.01	Yr04	1	1	4	1	5	4	2
C.01	Yr05	1	1	5	1	5	5	3
C.01	Yr06	1	1	6	1	5	6	4
C.01	Yr07	1	1	7	1	5	7	5
C.02	Yr08	2	1	8	1	5	8	1
C.02	Yr09	2	1	9	1	5	9	2
C.02	Yr10	2	1	10	1	5	10	3
	Yr00		0	0	0	5	0
	Yr01		0	1	0	5	1
	Yr02		0	2	0	5	2
	Yr03		0	3	0	5	3
	Yr04		0	4	0	5	4
	Yr05		0	5	0	5	5
D.01	Yr06	1	1	6	0	5	6	1
	Yr07		0	7	1	5	7
	Yr08		0	8	0	5	8
	Yr09		0	9	0	5	9
	Yr10		0	10	0	5	10

TableAfter

Yr become Horizontal the rest of columns stay Vertical and the intersection are the value in Column NoEE

I tried pivot Column (Yr and NoEE), after I filter, Groupby add Index Expand then Pivot, I did not get the correct result

I got this table. But Filling down or up fill more than one EENude

EENude

EE.Refill

PeriodNo

Yr01

Yr02

Yr03

Yr05

Yr06

Yr07

Yr08

Yr09

Yr10

Yr04

A.01

1

null

A.01

1

2

null

1

null

A.02

2

3

null

1

null

A.03

3

5

null

1

null

B.01

1

null

B.01

1

2

null

1

null

B.02

2

3

null

1

null

B.03

3

5

null

1

null

B.03

3

6

null

1

null

B.04

4

7

null

1

null

B.04

4

8

null

1

null

B.05

5

9

null

1

null

B.05

5

10

null

1

null

C.01

1

3

null

1

null

C.01

1

4

null

1

C.01

1

5

null

1

null

C.01

1

6

null

1

null

C.01

1

7

null

1

null

C.02

2

8

null

1

null

C.02

2

9

null

1

null

C.02

2

10

null

1

null

D.01

1

6

null

1

null

How to make EENude Unique and aligning the numbers under one each for each one? A.01 become unique and resulted as follow. Similar result for the Rest of EENude Row

EENude

EE.Refill

PeriodNo

Yr01

Yr02

Yr03

Yr05

Yr06

Yr07

Yr08

Yr09

Yr10

Yr04

A.01

1

null

Your Detail explanation is very much appreciated

Akash_Varuna · ‎02-25-2025

Hi @DSR Could you try this please

Ensure Clean Data:
- Verify that your table has no duplicate rows or unnecessary columns.
- Make sure EENude , Yr , and NoEE columns are present and correctly formatted.
Unpivot Other Columns:
- If there are additional year-like columns already present horizontally, unpivot them first to ensure your table is in the correct format.
- Select all year-related columns, right-click, and choose Unpivot Columns.
Pivot the Yr Column:
- In Power Query, select the Yr column.
- Click Transform → Pivot Column.
- When prompted for a value column, select NoEE as the value.
- Power Query will automatically create columns for each unique Yr value and populate them with the corresponding NoEE values.
- - Group by EENude:
    - Select the EENude column.
    - Go to Home → Group By.
    - In the Group By settings:
      - Group by: EENude.
      - Operations: Keep all other columns in the output.
  - Reorganize the columns according to your desire
    If this post helped please do give a kudos and accept this as a solution
    Thanks In Advance

View solution in original post

DSR · ‎02-26-2025

Hello Akash_Varuna, You Correctly Solve it. Thank you
I followed your suggestion:
"

Unpivot Other Columns:
- If there are additional year-like columns already present horizontally, unpivot them first to ensure your table is in the correct format.
- Select all year-related columns, right-click, and choose Unpivot Columns."

and It clean the year-like columns already present horizontally,

Here is the codes"
let
Source = Excel.CurrentWorkbook(){[Name="Tb_PrvRowCalc_inGrp"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Case.CC.EEName", type text}, {"PeriodNo", Int64.Type}, {"Yr", type text}, {"NoEE.inPrvPrd", Int64.Type}, {"Yr Move Out", Int64.Type}, {"Index.inGrp", Int64.Type}, {"NoEE", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Case.CC.EEName"}, {{"Details", each fx_RefertoPreviousRunningCalc("Tenure", _, "Index.inGrp", "Yr Move Out", "NoEE") , type table}}),
#"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"PeriodNo", "Yr", "NoEE.inPrvPrd", "Yr Move Out", "Index.inGrp", "NoEE", "Tenure", "EE.Refill", "EENude"}, {"PeriodNo", "Yr", "NoEE.inPrvPrd", "Yr Move Out", "Index.inGrp", "NoEE", "Tenure", "EE.Refill", "EENude"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Details",{"EENude", "EE.Refill", "Yr", "NoEE", "Case.CC.EEName", "PeriodNo", "NoEE.inPrvPrd", "Yr Move Out", "Index.inGrp", "Tenure"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Reordered Columns",{ "EENude", "Yr", "NoEE"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns1",{{"EENude", type text}, {"Yr", type text}, {"NoEE", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Yr]), "Yr", "NoEE", List.Sum),
#"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each ([EENude] <> ""))
in
#"Filtered Rows"

EENude	Yr00	Yr01	Yr02	Yr03	Yr04	Yr05	Yr06	Yr07	Yr08	Yr09	Yr10
A.01		1	1
A.02				1
A.03						1
B.01		1	1
B.02				1
B.03						1	1
B.04								1	1
B.05										1	1
C.01				1	1	1	1	1
C.02									1	1	1
D.01							1

View solution in original post

Akash_Varuna · ‎02-25-2025

Hi @DSR Could you try this please

Ensure Clean Data:
- Verify that your table has no duplicate rows or unnecessary columns.
- Make sure EENude , Yr , and NoEE columns are present and correctly formatted.
Unpivot Other Columns:
- If there are additional year-like columns already present horizontally, unpivot them first to ensure your table is in the correct format.
- Select all year-related columns, right-click, and choose Unpivot Columns.
Pivot the Yr Column:
- In Power Query, select the Yr column.
- Click Transform → Pivot Column.
- When prompted for a value column, select NoEE as the value.
- Power Query will automatically create columns for each unique Yr value and populate them with the corresponding NoEE values.
- - Group by EENude:
    - Select the EENude column.
    - Go to Home → Group By.
    - In the Group By settings:
      - Group by: EENude.
      - Operations: Keep all other columns in the output.
  - Reorganize the columns according to your desire
    If this post helped please do give a kudos and accept this as a solution
    Thanks In Advance

DSR · ‎02-26-2025

Hello Akash_Varuna, You Correctly Solve it. Thank you
I followed your suggestion:
"

Unpivot Other Columns:
- If there are additional year-like columns already present horizontally, unpivot them first to ensure your table is in the correct format.
- Select all year-related columns, right-click, and choose Unpivot Columns."

and It clean the year-like columns already present horizontally,

Here is the codes"
let
Source = Excel.CurrentWorkbook(){[Name="Tb_PrvRowCalc_inGrp"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Case.CC.EEName", type text}, {"PeriodNo", Int64.Type}, {"Yr", type text}, {"NoEE.inPrvPrd", Int64.Type}, {"Yr Move Out", Int64.Type}, {"Index.inGrp", Int64.Type}, {"NoEE", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Case.CC.EEName"}, {{"Details", each fx_RefertoPreviousRunningCalc("Tenure", _, "Index.inGrp", "Yr Move Out", "NoEE") , type table}}),
#"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"PeriodNo", "Yr", "NoEE.inPrvPrd", "Yr Move Out", "Index.inGrp", "NoEE", "Tenure", "EE.Refill", "EENude"}, {"PeriodNo", "Yr", "NoEE.inPrvPrd", "Yr Move Out", "Index.inGrp", "NoEE", "Tenure", "EE.Refill", "EENude"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Details",{"EENude", "EE.Refill", "Yr", "NoEE", "Case.CC.EEName", "PeriodNo", "NoEE.inPrvPrd", "Yr Move Out", "Index.inGrp", "Tenure"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Reordered Columns",{ "EENude", "Yr", "NoEE"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns1",{{"EENude", type text}, {"Yr", type text}, {"NoEE", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Yr]), "Yr", "NoEE", List.Sum),
#"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each ([EENude] <> ""))
in
#"Filtered Rows"

EENude	Yr00	Yr01	Yr02	Yr03	Yr04	Yr05	Yr06	Yr07	Yr08	Yr09	Yr10
A.01		1	1
A.02				1
A.03						1
B.01		1	1
B.02				1
B.03						1	1
B.04								1	1
B.05										1	1
C.01				1	1	1	1	1
C.02									1	1	1
D.01							1

Pivot columns to rows in power query - I have a table Yr and EE are Vertical. Want Yr HORIZONTAL

Helpful resources

Join us at the Microsoft Fabric Community Conference

Power BI Monthly Update - February 2025

Fabric Community Update - February 2025

Join us at the 2025 Microsoft Fabric Community Conference

Pivot columns to rows in power query - I have a table Yr and EE are Vertical. Want Yr HORIZONTAL

Helpful resources

Join us at the Microsoft Fabric Community Conference

Power BI Monthly Update - February 2025

Fabric Community Update - February 2025