The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi All
My raw data :-
GL_CODE,Amount,DESCRIPTION
1,10,REVENUE LOCAL
2,20, REVENUE OVERSEA
9,10,REVENUE INTER COY
10,5,COGS LOCAL
19,6,COGS OVERSEA
How to recode :-
GL_CODE 1-9 EQUAL REVENUE
GL_CODE 10-19 EQUAL COGS
Paul
Solved! Go to Solution.
Hi @Paulyeo11
I think you want to build a new column to create mapping for GL CODE column.
I build a table Like yours and have a test.
Dax:
Switch or IF function.
Dax_Recode =
SWITCH(TRUE(),MAX('Table'[GL_CODE])<=9,"REVENUE",MAX('Table'[GL_CODE])<=19,"COGS",BLANK())
Power Query Editor :
Use Add Conditional Column Function, you can copy my M Query in your advanced Editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0ABJBrmGufqGuCj7+zo4+SrE60UpGQFEjZCn/MNegYFdHBbCsJbpGT78Q1yAFZ/9IsDRYyhSInf3dg5EMNQTpM4OJQ01Uio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [GL_CODE = _t, Amount = _t, #"DESCRIPTION " = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"GL_CODE", Int64.Type}, {"Amount", Int64.Type}, {"DESCRIPTION ", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Recode", each if [GL_CODE] <= 9 then "REVENUE" else if [GL_CODE] <= 19 then "COGS" else null)
in
#"Added Conditional Column"
Result:
You can download the pbix file from this link: How to create mapping for GL CODE ?
If this reply still couldn't help you solve your problem, please show me the result you want, and your data model. This will make it easier for me to understand your requirement.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Paulyeo11
I think you want to build a new column to create mapping for GL CODE column.
I build a table Like yours and have a test.
Dax:
Switch or IF function.
Dax_Recode =
SWITCH(TRUE(),MAX('Table'[GL_CODE])<=9,"REVENUE",MAX('Table'[GL_CODE])<=19,"COGS",BLANK())
Power Query Editor :
Use Add Conditional Column Function, you can copy my M Query in your advanced Editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0ABJBrmGufqGuCj7+zo4+SrE60UpGQFEjZCn/MNegYFdHBbCsJbpGT78Q1yAFZ/9IsDRYyhSInf3dg5EMNQTpM4OJQ01Uio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [GL_CODE = _t, Amount = _t, #"DESCRIPTION " = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"GL_CODE", Int64.Type}, {"Amount", Int64.Type}, {"DESCRIPTION ", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Recode", each if [GL_CODE] <= 9 then "REVENUE" else if [GL_CODE] <= 19 then "COGS" else null)
in
#"Added Conditional Column"
Result:
You can download the pbix file from this link: How to create mapping for GL CODE ?
If this reply still couldn't help you solve your problem, please show me the result you want, and your data model. This will make it easier for me to understand your requirement.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Rico
Thank you for sharing your script with me.
may i know your approach in what way how it is better better then Fowmy approach ?
Paul
Hi @Paulyeo11
Fowmy builds two measures:
Measure Revenue calculate the Amount which 1<=GL CODE<=9, Measure COGS calculate the Amount which 10<=GL CODE<=20.
My Measure/M query show you a column if 1<=GL CODE<=9 equal to String " Revenue", if 10<=GL CODE<=20 equal to String "COGS".
Our measures show you different results, I hope they can help you solve your problem.
If our measures's result are either not what you require, you can show me more details. Such as show me a screenshot of the result you want.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Rico
Okay now i understand why your apporach is better , because you doing the Mapping seperately. which is more easy to maintain.
Are you able to share the GL sample PBIX file you did with me ? So i can learn from you.
Hi @Paulyeo11
You can download the pbix file from this link: How to create mapping for GL CODE ?
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Sir
Thank you very much
Paul
Hi @Paulyeo11
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
@Paulyeo11
You can create measures for the revenue and COGS as follows.
Revenue =
CALCULATE(
SUM(Table3[Amount]),
FILTER(
ALL(Table3[GL_CODE]),
Table3[GL_CODE] >= 1 && Table3[GL_CODE] <= 9)
)
COGS =
CALCULATE(
SUM(Table3[Amount]),
FILTER(
ALL(Table3[GL_CODE]),
Table3[GL_CODE] >= 10 && Table3[GL_CODE] <= 20)
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
270 | |
129 | |
123 | |
100 | |
92 |