cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jpalji Frequent Visitor
Frequent Visitor

Compare columns in power BI

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?

Capture.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Compare columns in power BI

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
Highlighted
Super User
Super User

Re: Compare columns in power BI

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Jpalji Frequent Visitor
Frequent Visitor

Re: Compare columns in power BI

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

Re: Compare columns in power BI

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Jpalji Frequent Visitor
Frequent Visitor

Re: Compare columns in power BI

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.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,065)