How to Duplicate a table created in DAX to be used in Power Query in order to make transformations?

Question

I'm looking to take a table that has been grouped in DAX:

Quality Table = 
    SUMMARIZECOLUMNS(
        'Knitting Defects with Machine'[Knitting Machine ],
        'Knitting Defects with Machine'[Knit Date],
        'Knitting Defects with Machine'[Defect Description],
        'Knitting Defects with Machine'[Defect Code],
        "Defect Percentage", [Defect Percentage],
        "Defect Quantity", [Defect Quantity],
        "Machine Total", [Machine Total]
    )

and bring it into Power Query. I know that I can group in Power Query but I have three important measures being summarized that I'm at a lost to recreate in Power Query.

Defect Percentage = Defect Qty (is where the code begins with 5) / Machine Total

Defect Percentage

= VAR Percentage = DIVIDE( [Defect Quantity] , [Machine Total] )
RETURN 

if( Percentage <.1, blank(), Percentage )

Defect Quantity

= CALCULATE(
    SUMX( 'Knitting Defects with Machine',
        'Knitting Defects with Machine'[Processed Qty]),
        left('Knitting Defects with Machine'[Defect Code])="5"
)

Machine Total:

= CALCULATE (
    SUMX (
        'Knitting Defects with Machine',
        'Knitting Defects with Machine'[Processed Qty]
    ),
    ALLEXCEPT (
        'Knitting Defects with Machine',
        'Knitting Defects with Machine'[Knitting Machine ],
        'Calendar'[Date]
    )
)

Here is a picture of my current output in DAX:

I need to get this output in Power Query because I have to do a lot to it from there: unpivot to a description/value row and then union a similar but different table related by machine. Unless I can do it all in DAX?

Alexis Olson Alexis Olson · Accepted Answer · 2019-08-05T18:05:42

It's not possible to feed calculated tables back into the query editor (unless you export them to a file first as an intermediate step somehow).

It's probably possible to do all the necessary transformations entirely in M or entirely in DAX but figuring out the best way forward is dependent on the particulars of your specific model.

How to Duplicate a table created in DAX to be used in Power Query in order to make transformations?

1 Answers