Forum Discussion
Pivot_unpivot
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.
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.