Power Query Calculation Help

[paulfink]

Hi Guys,

need some help with Power Query M Code as im new to it.

I want to make some columns and it has to be in Power Query as i need to ammend this into another table.

I want to make columns using these DAX formulas but alter them to M Code:

REV - VAL = 
VAR
REV = CALCULATE(SUM('Table1'[Value]), FILTER('Table1', 'Table1'[Name] = "Rev"))

VAR
VAL = CALCULATE(SUM('Table1'[Value]), FILTER('Table1', 'Table1'[Name] = "Val"))

Return
+REV - Direct

Value1 = [REV - VAL] / CALCULATE(SUM('Table1'[Value]), FILTER('Table1', 'Table1'[Name] = "Name1"))

Value2 = [Value1] - CALCULATE(SUM('Table1'[Value]), FILTER('Table1', 'Table1'[Name] = "Name2"))

How can i replicate these measures into M Code?

[Anonymous · Accepted solution]

Hi @paulfink ,'

I have built a simply data sample with your DAX code:

Please use the below codes(List.Sum function) to create Custom columns:

Rev=let _item = [Name] in List.Sum(Table.SelectRows(#"Changed Type", each [Name] = "Rev")[Value])

Val= let _item = [Name] in List.Sum(Table.SelectRows(#"Rev", each [Name] = "Val")[Value])

...

After doing all sum for each Name, the table will be like this:

Then:

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[paulfink]

Wow thats amazing, is it possible to have blank cells that dont relate to the filtered names? just so i can merge into one column