How to get the value in a column based on conditio…


Scenario:    

In Power BI we can easily get a value with a specific condition by DAX. Which, however, will be more complicated for M language beginners in Power Query. This blog will introduce you how to get the value in a column based on condition of another column in Power Query.

 

Example:    

RicoZhou_0-1663639340639.png

 

Expected result: 

RicoZhou_2-1663639376176.png

 

RicoZhou_3-1663639384857.png

 

  • Get the value of previous row in Column1.
  • For the same value in Column1, pick its maximum value in Column2.
  • For the same value in Column1, pick its first value in Column2.
  • Pick the first value in Column1 whose corresponding value in Column2 is greater than 5.
  • Extract rows with the value B in column 1.

 

Solution:  

 

  1. Add an index column in the table. 

RicoZhou_4-1663639412707.png

 

  1. Add a custom column or use code directly in other M expression that requires this result.

RicoZhou_5-1663639419415.png

 

Scenario 1: Get the value of previous row in Column1

RicoZhou_6-1663639452253.png

                                        RicoZhou_11-1663639610159.png

RicoZhou_7-1663639467125.png

                                                                                                      

code interpretation:

(x)=>x[Index]: This is a function in M language. The function-expression is:parameter-list ) function-return-type => function-body, parameter-list and return-type are optional. Here it will return a table containing only the rows that match a condition, similar with the expression “FILTER(ALL(‘Table’), <filter>)” in DAX.

Table.SelectRows(table, condition)[Column1]{0}: The Table.SelectRows function will return a filtered table, next add a column which will return a corresponding filtered list. Add an expression {number} after a list will return a particular record, the number in the symbol {} represents the value of the row number in the list minus 1. So here in the sample, {0} will return the first row in the list. As there’s only one row filtered based on the condition “[Index]=[Index]-1”, the previous value in the column return.

 

Here are more extended scenarios and solutions:

Scenario 2: For the same value in Column1, pick its maximum value in Column2

RicoZhou_8-1663639509375.png

                                     RicoZhou_11-1663639610159.png

RicoZhou_9-1663639540551.png

                                                                                                       

Scenario 3: For the same value in Column1, pick its first value in Column2

RicoZhou_14-1663639680126.png

                                     RicoZhou_11-1663639610159.png

RicoZhou_15-1663639687594.png

 

Scenario 4: Pick the first value in Column1 whose corresponding value in Column2 is greater than 5

RicoZhou_16-1663639703329.png

                                     RicoZhou_11-1663639610159.png

RicoZhou_17-1663639708532.png

 

Scenario 5: Extract rows with the value B in column 1

In this scenario, open a new blank query and copy  the below code.

RicoZhou_18-1663640010912.png

                                     RicoZhou_11-1663639610159.png

RicoZhou_19-1663640016568.png

 

Whole M syntax: 

Scenario 1 to Scenario 4:

 

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsIzALCcgywTOMgaznIEsUzjLDMxyAbIs4CxzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

    #"Added Custom" = Table.AddColumn(#"Added Index", "PreviousRow", each if [Index]=0 then "" else Table.SelectRows(#"Added Index",(x)=>x[Index]=[Index]-1)[Column1]{0}),

    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Max", each List.Max(Table.SelectRows(#"Added Index",(x)=>x[Column1]=[Column1])[Column2])),

    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "First", each Table.SelectRows(#"Added Index",(x)=>x[Column1]=[Column1])[Column2]{0}),

    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "SpecificCondition", each Table.SelectRows(#"Added Index",(x)=>x[Column2]>5)[Column1]{0})

in

    #"Added Custom3"

 

Scenario 5:

 

let

    Source = Table.SelectRows(Table,each [Index]>=Table.SelectRows(Table,(x)=>x[Column1]="B")[Index]{0}and [Index]<=List.Max(Table.SelectRows(Table,(x)=>x[Column1]="B")[Index]))

in

    Source

 

 

Hope this article will help people with similar questions.   

 

Author: Yan Jiang

Reviewer: Kerry Wang & Ula Huang



Source link

Be the first to comment

Leave a Reply

Your email address will not be published.


*