Create a lookup in powerbi power query

[K_Stevs1]

Hi,

Is there a way to get the text from 'column A' based on 'ref number' being the same and the previous date? 

e.g. 

extract         | Ref Number | status 

01/03/2023 |  12                | A

01/03/2023 |  132              | C

01/04/2023 |  12                | A

01/04/2023 |  132              | C

01/05/2023 |  12                | B

01/05/2023 |  132              | C

01/06/2023 |  12                | B

01/06/2023 |  132              | C

01/06/2023 |  12                | A

01/06/2023 |  132              | C

I would like to add two columns that show

A = Top status and C = bottom status,  Changes = up arrow, down arrow or - (for the same)

extract         | Ref Number | [Current] status  | Previous status  | Change

01/03/2023 |  12                | A                        | -                         | - 

01/03/2023 |  132              | C                        | -                         | -

01/04/2023 |  12                | A                        | A                        | ↔️  

01/04/2023 |  132              | C                        | C                        | ↔️

01/05/2023 |  12                | B                        | A                        | ⬇️

01/05/2023 |  132              | C                        | C                        | ↔️

01/06/2023 |  12                | B                        | B                        | ↔️

01/06/2023 |  132              | C                        | C                       | ↔️

01/06/2023 |  12                | A                        | B                       | ↗️

01/06/2023 |  132              | C                        | C                       | ↔️

Thank you for taking the time to read this post and for any help you maybe able to offer.

[CoreyP]

Thanks for providing some example data, but I'm still uncertain what you're hoping to accomplish. Can you provide some more specific details on what you're wanting to achieve?

[Fowmy]

@K_Stevs1 

Sorry, your question isn't clear, could you provde the expected result as well with few examples

[K_Stevs1]

Sorry, forgot to add it.

I would like to add two columns that show

A = Top status and C = bottom status,  Changes = up arrow, down arrow or - (for the same)

extract         | Ref Number | [Current] status  | Previous status  | Change

01/03/2023 |  12                | A                        | -                         | - 

01/03/2023 |  132              | C                        | -                         | -

01/04/2023 |  12                | A                        | A                        | -   

01/04/2023 |  132              | C                        | C                        | -

01/05/2023 |  12                | B                        | A                        | ⬇️

01/05/2023 |  132              | C                        | C                        | -

01/06/2023 |  12                | B                        | B                        | -

01/06/2023 |  132              | C                        | C                       | -

01/06/2023 |  12                | A                        | B                       | ⬆️

01/06/2023 |  132              | C                        | C                       | -

thanks

[v-xinruzhu-msft · Accepted solution]

Hi @K_Stevs1 

1.If you want to implement it in power query, you can put the following code to advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31jcyMDJW0lEyNAISjkqxOqiixiBhZ6iwCVbFJtgVm6IodsIQRVFshlWxGTGKHXEqjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [extract = _t, #"Ref Number" = _t, status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"extract", type date}, {"Ref Number", Int64.Type}, {"status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Ref Number"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1)
, type table }}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"extract", "status", "Index"}, {"extract", "status", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Previous status", each List.Min(Table.SelectRows(#"Expanded Data",(x)=>x[Ref Number]=[Ref Number] and x[Index]=[Index]-1)[status])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [Previous status]=null then null else if [Previous status]=[status] then "←→" else if [Previous status]<[status] then "⬇" else if [Previous status]>[status] then "⬆" else null),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Custom", "Change"}})
in
    #"Renamed Columns"

Output

2.After create the previous column ,It is better to use measure to format the previous column instead of create the change column , you can refer to the following measure.

Format =
SWITCH (
    TRUE (),
    ISBLANK ( MAX ( 'Table'[Previous status] ) ), 0,
    MAX ( 'Table'[Previous status] ) > MAX ( 'Table'[status] ), 1,
    MAX ( 'Table'[Previous status] ) = MAX ( 'Table'[status] ), 2,
    MAX ( 'Table'[Previous status] ) < MAX ( 'Table'[status] ), 3
)

Then if you put the previous column in a table viaual, set the conditional formatting and select icons

and then put the measure to it.

Output

Best Regards!

Yolo Zhu

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