Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
Hey @Anonymous ,
yes, it works with a small table and it will also work with a bigger table.
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
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!
Hey @Anonymous ,
yes, it works with a small table and it will also work with a bigger table.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |