Forum Discussion
Getting values from one table to another by matching rows
I have two different (massive) tables that update live from SharePoint lists. Each has different data but on the same projects. In order to do some analysis in my Power BI I need to get some data from one table to another, but matcing by several columns.
For example, Table A has columns:
Site | Project | Part | Shipping Date | ....(plus dozens more not relevant)
Table B has:
Site | Project | Part | ....(plus dozens more not relevant)
Many rows may have common Site, Project or Part, but the combination of the three is pretty unique, so I want to match a row in A with a row in B based on those three columns being identical, then when a match is found bring Shipping Date from A over to B on the matched row.
Is this possible with PBI?
TIA.
Hey,
to pull the shipping date from table A to Table B just create a new calculated column in table B like so:
Pull Shipping Date = LOOKUPVALUE( 'A'[Shipping Date] ,'A'[column A], 'B'[column A] ,'A'[column B], 'B'[column B] ,'A'[column C], 'B'[column C] )
This is the way how this DAX statement works:
For each row in table the values from column A to C are used as search values to retrieve the [Shipping Date] value from table A when there is a match in in the given columns from Table A.
Hopefully this clarifies the working of LOOKUPVALUE a little more.
Maybe you should give it just a try.
Regards,
Tom
4 Replies
Hey,
yes this is possible, you can try the DAX function LOOKUPVALUE(...): https://dax.guide/lookupvalue/
If there are more rows with differnt values that match the given criteria, you can replace this line:
... SELECTEDVALUE ( <Result_ColumnName>, <Alternate_Result> ), ...
with this
... aggregatefunction('lookuptable'[resultcolumnname]), ...where aggregatefunction has to be replaced with one of the aggregate functions like SUM, MAX, ... choose the one that best fits your needs.
Regards,
Tom
Thank you for the reply Tom. I am pretty new to PBI so maybe I am misundestanding this function but it seems to want a ResultColumn, SearchColumn and also a SearchValue. I don't want to enter a fixed search value because there are many thousands of projects and I just want to match like: If Table 1 / Column A matches Table 2 / Coumn A, and Table 1 / Column B matches Table 2 / Column B and Table 1 / Column C matches Table 2 / Column C then copy Table 2 / Column D to Table 1 on the corresponding (matching) row.
In this case PBI should not care what is in Columns A, B and C, just that if it finds matches between the tables.
Hey,
to pull the shipping date from table A to Table B just create a new calculated column in table B like so:
Pull Shipping Date = LOOKUPVALUE( 'A'[Shipping Date] ,'A'[column A], 'B'[column A] ,'A'[column B], 'B'[column B] ,'A'[column C], 'B'[column C] )
This is the way how this DAX statement works:
For each row in table the values from column A to C are used as search values to retrieve the [Shipping Date] value from table A when there is a match in in the given columns from Table A.
Hopefully this clarifies the working of LOOKUPVALUE a little more.
Maybe you should give it just a try.
Regards,
Tom