Pivot_unpivot

[BYonRo04]

Please help me convert the first table to the second table in Power BI.

table 1

Object Value	Date	User	Field Name	New Value
1234	30.09.2024	EX011111	FRGZU	X
1234	30.09.2024	EX011111	FRGZU	XX
1234	15.10.2024	ZT013201	FRGZU	XXX
1234	29.10.2024	I0172215	FRGKE	G
1234	29.10.2024	I0172215	FRGZU	XXXX
2345	30.06.2024	ZT010104	FRGZU
2345	30.06.2024	ZT010104	FRGKE	B
2345	01.07.2024	ZT010104	FRGZU	X
2345	01.07.2024	ZT010104	FRGKE	G
2345	01.07.2024	ZT010104	FRGZU	XX
3456	15.02.2024	ZT012213	FRGZU
3456	15.02.2024	ZT012213	FRGKE	B
3456	15.02.2024	ZT012213	FRGKE	G
3456	15.02.2024	ZT012213	FRGZU	X

Table 2

Object Value

Date 1

User 1

Field Name 1

New Value 1

Date 2

User 2

Field Name 2

New Value 2

Date 3

User 3

Field Name 3

New Value 3

Date 4

User 4

Field Name 4

New Value 4

Date 5

User 5

Field Name 5

New Value 5

1234

30.09.2024

EX011111

FRGZU

X

30.09.2024

EX011111

FRGZU

XX

15.10.2024

ZT013201

FRGZU

XXX

29.10.2024

I0172215

FRGKE

G

29.10.2024

I0172215

FRGZU

XXXX

2345

30.06.2024

ZT010104

FRGZU

30.06.2024

ZT010104

FRGKE

B

01.07.2024

ZT010104

FRGZU

X

01.07.2024

ZT010104

FRGKE

G

01.07.2024

ZT010104

FRGZU

XX

3456

15.02.2024

ZT012213

FRGZU

15.02.2024

ZT012213

FRGKE

B

15.02.2024

ZT012213

FRGKE

G

15.02.2024

ZT012213

FRGZU

X

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Object Value"}, {{"Count", each Table.AddIndexColumn(_,"Index",1)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Date", "User", "Field Name", "New Value", "Index"}, {"Date", "User", "Field Name", "New Value", "Index"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Count", {"Object Value", "Index"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Index", type text}}, "en-IN"),{"Attribute", "Index"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

Hope this helps.

[BYonRo04]

Thank you very much, Mathur.

The M code works very fast with a source data with more than 1000 rows.

[Ashish_Mathur]

You are welcome.

[v-pgoloju]

Hi @BYonRo04,

Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[v-pgoloju]

Hi @BYonRo04,

Has your issue been resolved, or do you require any further information? Your feedback is valuable to us. If the solution was effective, please mark it as 'Accepted Solution' to assist other community members experiencing the same issue.

Thnaks & Regards,

Prasanna kumar

[v-pgoloju]

Hi @BYonRo04 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[rohit1991]

Hi @BYonRo04 ,
To convert Table 1 into Table 2 in Power BI, you’ll need to use the Pivoting and Grouping functionality in Power Query. The goal is to transform multiple rows per "Object Value" into a single row, with each related record spread across new columns. In Power Query, start by loading the data and ensuring it's sorted by "Object Value" and "Date" (or by the original row order if needed). 

Then, group the table by "Object Value" and use the “All Rows” option to retain the detailed records. After grouping, add an Index column within each grouped table to number the entries (1, 2, 3, etc.), then expand the rows and pivot based on this index — transforming each row into its own set of columns (Date 1, User 1, Field Name 1, New Value 1, and so on). Finally, rename the columns accordingly. This approach effectively flattens the dataset so each object has all its related changes displayed in a single row, just like your Table 2 format.

[Greg_Deckler]

@BYonRo04 If you want this in a visual you might be able to use use a Matrix visual. Otherwise, do you want to do this in Power Query or DAX? Finally, if you want it as an actual table in your model, it is definitely not an optimal table layout for Power BI.

[BYonRo04]

Unfortunately, we have to adopt another solution because for the pivoting in query, Power BI uploaded approx 30 GB and I had to stop it because it didn't finish uploading.

[v-pgoloju]

Hell0 @BYonRo04 ,

If the dataset size is large, consider using DirectQuery mode or a Composite Model to manage performance and memory usage effectively.

DirectQuery: Keeps data in the source, reducing Power BI file size.

Composite Model: Combines DirectQuery for large fact tables and Import mode for smaller dimension tables.

As an alternative approach, use Paginated Reports when:

You need detailed, tabular, or export-friendly reports.

You want to handle large datasets efficiently without loading everything into memory.

Thank you.