Replace values with a certain value from another table

[Anonymous]

Hello everyone,

This is most probably an easy problem to solve but I can't get the hang of it...

I have a GL_Entry table where 2 columns look like this:

GL_Account_No       |     Global_Dimension

 8753123                            USFN_ASDF

 7654412                            USFN_ZXCVB

 66531278                          USFN_PLMOKN

Also, I have a table Transaction Dimensions with those columns:

GL_Acc_Start     |      GD_Actual_Value

 765                              USFN_QWERTY

 665                              USFN_OZN

What I'm trying to do is to make a dax function that replaces the Global Dimension values where the GL_Account_No starts with a certain substring of characters.

A rough example is that for every GL_Account_No that starts with 665 the Global Dimension value will be replaced with a corresponding value from the second table, in this case that's USFN_OZN. So, all rows with GL_Account_No that is starting with 665 will now have the Global Dimension value set to USFN_OZN.

If I want to replace the values with hardcoded text.. it works just fine.. But I want to get the values from a second table used specially for this.

#"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [GD1],each if Text.StartsWith([GL_ACC_NO], "665") then "hardcodedtext" else [GD1],Replacer.ReplaceText,{"GD1"})

This means that this expression should look like this:

#"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [GD1],each if Text.StartsWith([GL_ACC_NO], "665") then *get value from table Transaction Dimensions where GL_Acc_Start = 665* else [Global_Dimension],Replacer.ReplaceText,{"Global_Dimension"})

I've tried doing this with FIRSTNONBLANK but it doesn't get recognized...

I would appreciate any kind of help with this matter, thank you a lot for your time!

[selimovd · Accepted solution]

Hey @Anonymous ,

if you want to do that in Power Query you can extract the first 3 characters as a new column and then merge that with the other column.

Check the following example I created:

https://www.swisstransfer.com/d/0cce8983-d6a5-4a78-928a-32ee24e1905d

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[selimovd · Accepted solution]

Hey @Anonymous ,

if you want to do that in Power Query you can extract the first 3 characters as a new column and then merge that with the other column.

Check the following example I created:

https://www.swisstransfer.com/d/0cce8983-d6a5-4a78-928a-32ee24e1905d

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hello,

I really appreciate the help but will this work automatically for new data that comes into the GL_Entry table?

My real case scenario here looks like this:

I've got a lot of entries into the GL Entry table ( 500k pretty much ) but for certain account no. I want to change the Global Dimension value with a value from a small table configured specially for this ( Transaction Dimensions ).

For example I have 20 entries where the GL account starts with XYZ, for those I want to change the Global Dimension value with the one that exists in Transaction Dimensions table ( the corresponding value for XYZ of course ).

Sorry if I got your solution wrong, I really appreciate your help.

Is it even possible to do what I'm asking?

Have a great day!

[selimovd · Accepted solution]

Hey @Anonymous ,

yes, it works with a small table and it will also work with a bigger table.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic