How to create mapping for GL CODE ?

[Paulyeo11]

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

[v-rzhou-msft · Accepted 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. 

[Paulyeo11]

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

[v-rzhou-msft]

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. 

[Paulyeo11]

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.

[v-rzhou-msft]

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. 

[Paulyeo11]

Hi Sir

Thank you very much

Paul

[v-rzhou-msft]

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

[Fowmy]

@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 🙂

YouTube  LinkedIn