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.

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
1 year ago

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'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.