Pulling multiple columns from one (query, tables) into another according to multiple conditions

[HMK]

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

[TheoC · Accepted solution]

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

[HMK]

Thanks for your great support

[Ashish_Mathur]

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.

[HMK]

@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]

Download the revised PBI file from here.

Hope this helps.

 

[HMK]

It doesn't work correctly, because the 3rd row in T2 shall match the 11th row in T1 according to the condition but it seems it's blank.
For the latest part of the condition can I write if insted of making matching betwen the two columns (Left('T1'[P02]) = 'T2'[P02]) can I write the values in the formula directly e,g: Left('T1'[P02]) = "acd" or "zip"..etc. ?
T1T2

[Ashish_Mathur]

Hi,

You may download the revised PBI file from here.

Hope this helps.

[HMK]

Thanks for your great support

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[TheoC]

@HMK here is the solution you are after in the attached. Please let me know if you need anything futher mate!

You can follow the applied steps in Power Query and you will see the output is performed without the need for front-end / unnecessary coding which ensures a far more efficient outcome than you would otherwise experience when using DAX.

Do not hesitate to touch base with any other questions. 

Theo 🙂

[Ashish_Mathur]

It should still work.  If it does not, then share the download link of the PBI file.

[HMK]

I need the "First" = Left('T1'[P02], LEN('T2'[P02])).
And here you can find the PBI file.
Thanks in Advance.

[HMK]

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

[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#M7710...

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

[HMK]

Hi @TheoC the  'T1'[P02] should start with 'T2'[P02] because  'T2'[P02] contains just the (purchase code) and  'T1'[P02] contains : (purchase code, name, city, date) without the comma symbol
I need the "First" = Left('T1'[P02], LEN('T2'[P02])).
here you can find the PBI file.
Thanks in Advance

[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