Forum Discussion

BYonRo04's avatar
BYonRo04
Regular Visitor
1 year ago
Solved

Pivot_unpivot

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 EX...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    1 year ago

    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's avatar
BYonRo04
Regular Visitor
1 year ago

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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.