Forum Discussion
Pulling multiple columns from one (query, tables) into another according to multiple conditions
Hello experts,
I'm trying to pull values from column "P05" & "P07" in table 2 to table 1, if the values of the following columns "P00" & "P01" in both tables are identical, and if the values of column "P02" in table 1 begins with the value of column "P02" in table 2.
In another way:
IF 'T1'[P00] = 'T2'[P00] && 'T1'[P01] = 'T2'[P01] && Left('T1'[P02]) = 'T2'[P02], then Get values of columns 'T2'[P05] , 'T2'[P07] and insert in T1, else "null".
  
could you please guid me how to write it in dax?
Thanks in advance & Best regards
Hi @HMK
I've updated the PBIX for you in the attached. I've done it all in Power Query by creating a new key and then Appending the tables into a new table. PBIX file is attached.
The appended table (new table) looks like below. You can change / adjust as you want in Power Query, too, by just deleting whatever columns, etc, you don't want.
Let me know if you need anything else 🙂
Theo
16 Replies
Hi TheoC ,
I updated the post and added some photos, also below you can find the example file from my drive.
need to use left because the 'T2'[P02] will containt the purschase code and 'T1'[P02] contains purchase code, date, city without cooma or space.
can I make the number of charecters in left statment equal to the number of charecters in 'T2'[P02] eg, LEFT ( 'T1'[P02] = 'T2'[P02], Len('T2'[P02])?
could you please check if this formula would be fine?
https://drive.google.com/drive/folders/1GFoD36fzRmCZ73O9Xwgs4dXceEvJleiL?usp=sharing
pull eact value of 'T2'[P05] and 'T2'[P07] to T1 IF
'T1'[P00] = 'T2'[P00] && 'T1'[P01] = 'T2'[P01] && (Left('T1'[P02]) = 'T2'[P02],Len('T2'[P02])), then Get values of columns 'T2'[P05] , 'T2'[P07] and insert in T1, else "null".
Thanks in advance- Ashish_Mathur
Super User
Hi,
Try these calculated column formulas in T1
First = Left('T1'[P02])
P05 value = calculate(max('T2'[P05]),filter('T2','T2'[P00]=earlier('T1'[P00])&&'T2'[P01]=earlier('T1'[P01])&&'T2'[P02]=earlier('T1'[First])))
Hope this helps.
Ashish_Mathur thank for your proposal but it doesn't works because I have a text value in 'T2'[P05] so how can I get the exact value of P05 or find first instead of calculat max?
Thanks in advance.
T2T1- Ashish_Mathur
It should still work. If it does not, then share the download link of the PBI file.
- TheoC
Community Champion
Hi @HMK
I've updated the PBIX for you in the attached. I've done it all in Power Query by creating a new key and then Appending the tables into a new table. PBIX file is attached.
The appended table (new table) looks like below. You can change / adjust as you want in Power Query, too, by just deleting whatever columns, etc, you don't want.
Let me know if you need anything else 🙂
Theo
Thanks for your great support
- TheoC
Hi HMK
Just a heads up, it might be easier to answer your question with some sample data and by following these prompts: https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/2066986#M771079
In terms of your formula, is there a reason that the last AND condition is LEFT ( 'T1'[P02] = 'T2'[P02]... ? Normally the "LEFT" would require you to add 1) a number of characters and 2) a closing bracket ) to end it.
If you can provide some sample data, it may be easier to provide a response.
Cheers in advance!
Theo
- TheoC
Hi HMK
I've updated the PBIX for you in the attached. I've done it all in Power Query by creating a new key and then Appending the tables into a new table. PBIX file is attached.
The appended table (new table) looks like below. You can change / adjust as you want in Power Query, too, by just deleting whatever columns, etc, you don't want.
Let me know if you need anything else 🙂
Theo
