Convert Measure to Custom Column in Power Query

[Anonymous]

Hi Experts

How would you convert the following into a custom Column in Power Query...

Column = 
VAR T1 = FILTER(Input,Input[Year]=M[Year]&&Input[BB]=M[BB]&&Input[Time Period]=M[Time Period]&&Input[Terms]="BTMT Fee")
RETURN
IF(M[M Column]="M1",MAXX(T1,[Value]))

sample file

https://www.dropbox.com/s/7vdna17ffee0pf1/Test_2.pbix?dl=0   

[Vera_33 · Accepted solution]

Hi @Anonymous 

Here is a way to do in M, please test the performance, GROUPBY your original Input table and find Max, buffer it, then try to look up, it might be slow if you have lots of data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdi9SsRAFIDRVwkBuy0y/zOlFnbbbbdsobBWgrJi4ds773AGQrik+KpDkrnX636E7fX+/vh9e/xt8QhxP+0vl/NlPrzP8XjaPr4e25hjCMe8z+v5+/vz/jOH87HfTlYIXIhcSFzIXChcqFxoXOhcGC5qAUpXGZxlcJfBYQaXGZxmcJvBcQbXGV1nXPDOdJ3RdUbXGV1ndJ3RdUbXGV1ncp3JdaYFn3TXmVxncp3JdSbXmVxncp3ZdWbXmV1nXvDH6Tqz68yuM7vO7Dqz6yyus7jO4jqL6ywLDkSus7jO4jqL6yyus7rO6jqr66yus7rOuuC87jqr66yus7rO5jqb62yus7nO5jqb62wL1kmus7nO5jq76+yus7vO7jq76+yus7vOvmDb6Tq76xyuc7jO4TqH6xyuc7jO4TqH6xwLlvErtvGT5+0f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Terms = _t, BB = _t, #"Time Period" = _t, Fee = _t, #"Brand " = _t, #"M Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", type date}, {"Terms", type text}, {"BB", type text}, {"Time Period", Int64.Type}, {"Fee", type text}, {"Brand ", type text}, {"M Column", type text}}),
    newInput = Table.Buffer( Table.Group(Table.SelectRows(Input,each [Terms]="BTMT Fee"),{"Year","BB","Time Period"},{{"Test", each List.Max([Value]), type number}})),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom",  (x)=>  
    if x[M Column]="M1" then Table.SelectRows(newInput, each [Year] = x[Year] and [BB]=x[BB] and [Time Period] = x[Time Period])[Test]{0} else 0)
in
    #"Added Custom"

[AilleryO]

Hi,

As far as i can understand your situation, it is impossible.

In Power Query the relations are not implemented, they are in Power BI Desktop, so in PQ you will calculate only with columns coming from the same table.

Desktop after installing your relations allows you to combine informations from different tables.

Keep in mind that columns are made for iterative calculation (line by line), whereas Measures are made to work on columns.

To make it simple I could say, if your calculation is horizontal use calculated columns, if it's vertical then use measure.

If you can have all informations in one table then your problem should be solved with imbricated IF functions.

More infos :

https://www.sqlbi.com/articles/comparing-dax-calculated-columns-with-power-query-computed-columns/

Hope I understood your question, otherwise give us more informations on your problem.

Have a nice WE

[amitchandak]

@Anonymous , This not going to be one step process. Copy Table/column, filter, and merge the data

Refer: https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/

https://www.myonlinetraininghub.com/excel-power-query-vlookup