Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dedelman_clng
Community Champion
Community Champion

Replace UNION/SUMMARIZE with M code ?

Here is my original table:

 

Original.PNG

 

 

 

 

 

And I want to re-format it like this:

 

Final.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

(etc.)

 

I wrote a hellacious calculated table in DAX, but I'm thinking there must be a way to do this in M so I don't have the entire table in memory twice.  I tried the Unpivot PowerBI functions several different ways but couldn't get it right. 

 

Can anyone help me with the M code, or if I'm missing something simple in the Edit Query window?

 

Thanks,

David

 

Here is the DAX for reference:

 

DPS =
UNION (
    SUMMARIZE (
        'Daily Production Schedule',
       'Daily Production Schedule'[Production_Date],
        'Daily Production Schedule'[Storage_Name],
        'Daily Production Schedule'[Train_Code],
        "Measure Type", "Effective Capacity",
        "UoM", "MMBTU",
        "Value", SUM ( 'Daily Production Schedule'[Effective_Capacity_MMBTU] )
    ),
    SUMMARIZE (
       'Daily Production Schedule',
       'Daily Production Schedule'[Production_Date],
        'Daily Production Schedule'[Storage_Name],
        'Daily Production Schedule'[Train_Code],
       "Measure Type", "Effective Capacity",
        "UoM", "M3",
        "Value", SUM ( 'Daily Production Schedule'[Effective_Capacity_M3] )
    ),
    SUMMARIZE (
        'Daily Production Schedule',
       'Daily Production Schedule'[Production_Date],
        'Daily Production Schedule'[Storage_Name],
        'Daily Production Schedule'[Train_Code],
        "Measure Type", "Design Capacity",
        "UoM", "MMBTU",
        "Value", SUM ( 'Daily Production Schedule'[Design_Capacity_MMBTU] )
    ),
    SUMMARIZE (
       'Daily Production Schedule',
       'Daily Production Schedule'[Production_Date],
        'Daily Production Schedule'[Storage_Name],
        'Daily Production Schedule'[Train_Code],
        "Measure Type", "Effective Capacity",
        "UoM", "M3",
        "Value", SUM ( 'Daily Production Schedule'[Design_Capacity_M3] )
    ),
    SUMMARIZE (
       'Daily Production Schedule',
       'Daily Production Schedule'[Production_Date],
        'Daily Production Schedule'[Storage_Name],
        'Daily Production Schedule'[Train_Code],
        "Measure Type", "Forecast Qty",
        "UoM", MAX ( 'Daily Production Schedule'[Forecast_Qty_1_UoM] ),
        "Value", SUM ( 'Daily Production Schedule'[Forecast_Qty_1] )
    ),
    SUMMARIZE (
       'Daily Production Schedule',
       'Daily Production Schedule'[Production_Date],
        'Daily Production Schedule'[Storage_Name],
        'Daily Production Schedule'[Train_Code],
        "Measure Type", "Forecast Qty",
        "UoM", MAX ( 'Daily Production Schedule'[Forecast_Qty_2_UoM] ),
        "Value", SUM ( 'Daily Production Schedule'[Forecast_Qty_2] )
    ),
    SUMMARIZE (
       'Daily Production Schedule',
       'Daily Production Schedule'[Production_Date],
        'Daily Production Schedule'[Storage_Name],
        'Daily Production Schedule'[Train_Code],
        "Measure Type", "Forecast Qty",
        "UoM", MAX ( 'Daily Production Schedule'[Forecast_Qty_3_UoM] ),
        "Value", SUM ( 'Daily Production Schedule'[Forecast_Qty_3] )
    )
)
2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

 

In the Query Editor, select the first 3 columns > right click and select Unpivot other columns.  Right click on the Attribute column > Split column > By delimiter.  Specify the delimiter as _.  Rename the columns.  Click on Close and Apply.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Hi Ashish -

 

That technique worked for the 4 columns where the UoM is in the column header, but not for the ones where the value and UoM are in different columns.  However, starting with your suggestion I did the following:

 

- Copied the original query into a second query.

- On the original query, removed the columns that have quantity and UoM separately

- Unpivoted the 4 column with UoM in the header, then split the "Attribute" column on "_" (as you had said)

 

- In the 2nd query, removed the columns unpivoted previously

- Merged the Quantity and UoM columns with "=" (3 separate merge operations)

- Unpivoted the new merged columns

- Split the "Value" column on "="

- Split the "Attibute" column on "_" and removed the second column (values in that column were now UoM1, UoM2, UoM3)

- Appended the second query onto the first

 

Thanks for getting me pointed in the right direction!

David

 

 

 

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

 

In the Query Editor, select the first 3 columns > right click and select Unpivot other columns.  Right click on the Attribute column > Split column > By delimiter.  Specify the delimiter as _.  Rename the columns.  Click on Close and Apply.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish -

 

That technique worked for the 4 columns where the UoM is in the column header, but not for the ones where the value and UoM are in different columns.  However, starting with your suggestion I did the following:

 

- Copied the original query into a second query.

- On the original query, removed the columns that have quantity and UoM separately

- Unpivoted the 4 column with UoM in the header, then split the "Attribute" column on "_" (as you had said)

 

- In the 2nd query, removed the columns unpivoted previously

- Merged the Quantity and UoM columns with "=" (3 separate merge operations)

- Unpivoted the new merged columns

- Split the "Value" column on "="

- Split the "Attibute" column on "_" and removed the second column (values in that column were now UoM1, UoM2, UoM3)

- Appended the second query onto the first

 

Thanks for getting me pointed in the right direction!

David

 

 

 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.