Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to create mapping for GL CODE ?

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

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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. 

8 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anonymous 

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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.