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 ValueDateUserField NameNew Value
123430.09.2024EX011111FRGZUX
123430.09.2024EX011111FRGZUXX
123415.10.2024ZT013201FRGZUXXX
123429.10.2024I0172215FRGKEG
123429.10.2024I0172215FRGZUXXXX
234530.06.2024ZT010104FRGZU 
234530.06.2024ZT010104FRGKEB
234501.07.2024ZT010104FRGZUX
234501.07.2024ZT010104FRGKEG
234501.07.2024ZT010104FRGZUXX
345615.02.2024ZT012213FRGZU 
345615.02.2024ZT012213FRGKEB
345615.02.2024ZT012213FRGKEG
345615.02.2024ZT012213FRGZUX

 

Table 2

Object ValueDate 1User 1Field Name 1New Value 1Date 2User 2Field Name 2New Value 2Date 3User 3Field Name 3New Value 3Date 4User 4Field Name 4New Value 4Date 5User 5Field Name 5New Value 5
123430.09.2024EX011111FRGZUX30.09.2024EX011111FRGZUXX15.10.2024ZT013201FRGZUXXX29.10.2024I0172215FRGKEG29.10.2024I0172215FRGZUXXXX
234530.06.2024ZT010104FRGZU 30.06.2024ZT010104FRGKEB01.07.2024ZT010104FRGZUX01.07.2024ZT010104FRGKEG01.07.2024ZT010104FRGZUXX
345615.02.2024ZT012213FRGZU 15.02.2024ZT012213FRGKEB15.02.2024ZT012213FRGKEG15.02.2024ZT012213FRGZUX    

 

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.

     

10 Replies

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

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    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

      Thank you very much, Mathur.

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