cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Route217
Post Prodigy
Post Prodigy

Convert Measure to Custom Column in Power Query

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   

1 ACCEPTED SOLUTION
Vera_33
Solution Sage
Solution Sage

Hi @Route217 

 

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

Vera_33_0-1617369413767.png

 

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"

 

 

View solution in original post

3 REPLIES 3
Vera_33
Solution Sage
Solution Sage

Hi @Route217 

 

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

Vera_33_0-1617369413767.png

 

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"

 

 

View solution in original post

AilleryO
Continued Contributor
Continued Contributor

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
Super User IV
Super User IV

@Route217 , 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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors