cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Laurentiu
Frequent Visitor

Replace values with a certain value from another table

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!

2 ACCEPTED SOLUTIONS
selimovd
Community Champion
Community Champion

Hey @Laurentiu ,

 

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
 

View solution in original post

selimovd
Community Champion
Community Champion

Hey @Laurentiu ,

 

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
 

 

View solution in original post

3 REPLIES 3
selimovd
Community Champion
Community Champion

Hey @Laurentiu ,

 

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
 

View solution in original post

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
Community Champion
Community Champion

Hey @Laurentiu ,

 

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
 

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors