Replace UNION/SUMMARIZE with M code ?

Here is my original table:

And I want to re-format it like this:

(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] )
    )
)

Need Help

Ashish_Mathur
8 years ago
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.
dedelman_clng
8 years ago
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

3 Replies

Ashish_Mathur
Super User
8 years ago
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.
- dedelman_clng
  Community Champion
  8 years ago
  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
  - Ashish_Mathur
    Super User
    8 years ago
    You are welcome.

Forum Discussion

Replace UNION/SUMMARIZE with M code ?

3 Replies

Related content

DISTINCT UNION & Summarized Combined.

Union And Summarize Table

Calculated Table UNION and SUMMARIZE?

Summarize after union table

Replace NULL