Forum Discussion
How to create mapping for GL CODE ?
Hi Anonymous
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 Anonymous
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 Anonymous
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.