Compare columns in power BI

[Jpalji]

Hi,

I have to create a new column(col4), when Col1 cell match's with Col3 cell then Col4 should create and data from Col2 as shown below (Col1 has ref2 and Col3 has Ref2 as there is a match Col4 should Col2 Value 20), Please advise me in this. Thanks in advance.

How can we create in Power BI desktop?

[MFelix · Accepted solution]

Hi @Jpalji ,

You have 2 options trough the Query editor or in DAX.

DAX:

• Add the following column to the table:

Col4 = LOOKUPVALUE('Table'[Col2];'Table'[Col1];'Table'[Col3])

Query Editor:

• Do a merge of the table with itself
• On the merge you should match the Col3 with Col1 left outer join on col3
• Expand the resulting column and select col2 then rename:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNM1TSUTI0MACSQI6RUqxONISho2QEEoQJGAPZpqYQVWYwQRMg39wCSRVIgaUhkoAZkG1iChaIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Column3"}, #"Changed Type", {"Column1"}, "Changed Type", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Column2"}, {"Changed Type.Column2"})
in
    #"Expanded Changed Type"

Regards,

MFelix

[Jpalji]

Hi MFelix,

Thanks for reply, when I try to create a dax query as suggested power is showing error "A table of multiple values was supplied where a single value was expected". Please advise.

Thanks in advance.

[MFelix]

Hi @Jpalji ,

Are you creating a column or a measure?

This is a column to be created on your table not a measure.

Regards,

MFelix

[Jpalji]

Hi @MFelix ,

I have created a column but it is throwing the same error, but it worked when I duplicated table and created a column as advised.

Thanks a lot, @MFelix . 

Have a nice weekend!!

Once again thanks.