Merge 2 columns and find the common values

[haykp]

Dear Community,

I am freshmen in PowerBI, please help to solve one task with powerBI.

So I have 2 different lists with {Code,Price} values.

I need to compare these lists i.e.

a) find all elements with same Code and compare the prices

b) find all missing elements in List1

c) find all extra elements in List1

I think these 3 items are someohow connected, thats why writing in one topic.

These are my lists example:

Lists1:

List2:

Can someone please point me what direcction to go?

Thanks

Hayk

[Greg_Deckler · Accepted solution]

@haykp Maybe:

Calculated Table Missing List 1 =
  VAR __List1 = SELECTCOLUMNS('List1', "__Code", [Code])
  VAR __List2 = SELECTCOLUMNS('List1', "__Code", [Code])
  VAR __Return = EXCEPT(__List1, __List2)
RETURN
  __Result



Calculated Table Missing List 2 =
  VAR __List1 = SELECTCOLUMNS('List1', "__Code", [Code])
  VAR __List2 = SELECTCOLUMNS('List1', "__Code", [Code])
  VAR __Return = EXCEPT(__List2, __List1)
RETURN
  __Result



Compare Measure =
  VAR __Code = MAX('List1'[Code]) //assumes List1[Code] is in your visual
  VAR __Price = MAX('List1'[Price])
  VAR __Price2 = MAXX(FILTER('List2', [Code] = __Code), [Price])
  VAR __Result = __Price - __Price2
RETURN
  __Result

[amitchandak]

@haykp , you can merge the two table in power query using full outer or left anti to check what is missing

 https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Or create a common table code from both tables and then you can compare there price using measures

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...

Power BI- Power Query: When I asked you to create common tables: https://youtu.be/PqfGW6pl1Sw