Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power query for column caculation

Hi,  

I  have raw data as below, each month has two values C and A 

I need to create a new row "DO" in my final table,   the DO value is related to Row A and Row C as shown below, Mar.2022 DO equals to summation of A of (Mar2022,Dec2021,Sep2021,Jun2021,Mar2021  (3month interval based on current selected month)) divided by constant and divided by summation of C of last 12 month.   The calculation rule applies to other months, as months change, the denominator and dividend changes (  it can be easily achieved in excel by dragging the formula to other columns)  

Seondly, I need three extra columns as below,  vsLM: calculate the difference on C,A,DO between latest month and the month before   vsLY: calculate the difference on C,A,DO between latest month and same month last year   vsLYDec calculate the difference on C,A,DO between latest month and December of last year.    You can find the detailed calculation in attached excel file

Please advise how I can achive above in power query    Thanks a lot 

I have no idea how to upload my excel file in the forum so I paste the raw table and output table below. Let me know if you are not clear with the logic

This is the raw data

RawData202011202012202101202102202103202104202105202106202107202108202109202110202111202112202201202202202203
C2,3482,4982,2342,6482,7912,7432,6692,9442,8852,8002,7862,9862,6272,8532,5722,5903,513
A11,03211,04411,05110,34511,04811,16611,66111,80011,07710,40010,1969,88510,00610,43310,80811,62412,829

 

This is the table output, 

FinalData202011202012202101202102202103202104202105202106202107202108202109202110202111202112202201202202202203vsLMvsLYvsLYDec
C2,3482,4982,2342,6482,7912,7432,6692,9442,8852,8002,7862,9862,6272,8532,5722,5903,51336%26%23%
A11,03211,04411,05110,34511,04811,16611,66111,80011,07710,40010,1969,88510,00610,43310,80811,62412,82910%16%23%
DO#REF!#REF!#VALUE!#REF!#REF!#VALUE!#REF!#REF!#VALUE!#REF!#REF!#VALUE!82838181832%#REF!0%
  • Here is one way of doing it:

     

     

     

    let
    
    //these three lines are just reading in the data
    //replace them with whatever you use before you start the adding row and columns stuff
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY5LDoMhCISv0rhmwUuUZdNjmP/+16g69ABNTL4BBpy12qdRUzKfl56gml9G9UcK6IZ+5GU6fHN2kBm+GZgXQwfmHft9KJjHb9TF2kOrvXclQmxa4t4/op8Awjtp/40mhERARAgEUhzPGNhydJgkjzkr8G4wR1nMICbX4dD7+U6tedO9dvn3e54v", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RawData = _t, #"202011" = _t, #"202012" = _t, #"202101" = _t, #"202102" = _t, #"202103" = _t, #"202104" = _t, #"202105" = _t, #"202106" = _t, #"202107" = _t, #"202108" = _t, #"202109" = _t, #"202110" = _t, #"202111" = _t, #"202112" = _t, #"202201" = _t, #"202202" = _t, #"202203" = _t]),
        #"Remove Last Row" =  Table.RemoveLastN(Source,1),
        #"Previous Step" = Table.TransformColumnTypes(#"Remove Last Row", List.Transform(List.RemoveFirstN(Table.ColumnNames(#"Remove Last Row"),1),
            each {_, Int64.Type})),
    
    
    //calculate DO Row
      // Change #"Previous Step" to whatever it really is in your code
        cRow = List.RemoveLastN(List.Reverse(Record.ToList(#"Previous Step"{0})),1),
        aRow = List.RemoveLastN(List.Reverse(Record.ToList(#"Previous Step"{1})),1),
    
        doRowRev = List.Generate(
            ()=>[do=List.Sum(List.Range(List.Alternate(aRow,2,1,1),0,5)) / 0.02 /
                     List.Sum(List.Range(cRow,0,12)), 
                  idx=0],
            each [idx] < List.Count(aRow)-12,
            each [do=List.Sum(List.Range(List.Alternate(List.RemoveFirstN(aRow,[idx]+1),2,1,1),0,5)) / 0.02 /
                        List.Sum(List.Range(cRow,[idx]+1,12))
            
            , idx=[idx]+1],
            each [do]
                        ),
    
    //add DO Row to table
        //add enough nulls at the end + the Row label
        addNulls = Record.FromList(List.InsertRange(List.Reverse(doRowRev),0,{"DO"} & 
            List.Repeat({null},Table.ColumnCount(#"Previous Step")-List.Count(doRowRev)-1 )), Table.ColumnNames(#"Previous Step")),
        addDO = Table.FromRecords(Table.ToRecords(#"Previous Step") & {addNulls}),
    
    //set data types
    //   I set as number but you could use integer if you prefer, as shown in your screen shot
        typeIt=Table.TransformColumnTypes(addDO, {{"RawData", type text}} & List.Transform(
            List.RemoveFirstN(Table.ColumnNames(addDO),1), each {_, type nullable number}
        )),
    
    //add the extra columns
        thisMonth = List.Last(Table.ToColumns(typeIt)),
        lastYear = List.Last(List.RemoveLastN(Table.ToColumns(typeIt),12)),
        lastMonth = List.Last(List.RemoveLastN(Table.ToColumns(typeIt))),
        lastDec = Table.Column(typeIt,Text.From(Number.From(Text.Start(List.Last(Table.ColumnNames(typeIt)),4))-1) & "12"),
        newCols = List.Generate(
            ()=>[lm=(thisMonth{0}-lastMonth{0})/lastMonth{0}, 
                ly = (thisMonth{0}-lastYear{0})/lastYear{0},
                ld = (thisMonth{0}-lastDec{0})/lastDec{0},
                idx = 0],
            each [idx]<3,
            each [lm=(thisMonth{[idx]+1}-lastMonth{[idx]+1})/lastMonth{[idx]+1}, 
                ly = (thisMonth{[idx]+1}-lastYear{[idx]+1})/lastYear{[idx]+1},
                ld = (thisMonth{[idx]+1}-lastDec{[idx]+1})/lastDec{[idx]+1},
                idx = [idx]+1],
            each Record.FromList({[lm],[ly],[ld]},{"vsLM","vlLY","vsLYDec"})
            ),
    
        #"Added Columns" = Table.FromColumns(Table.ToColumns(typeIt) & Table.ToColumns(Table.FromRecords(newCols)),
            Table.ColumnNames(Source) & {"vsLM","vsLY","vsLYDec"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Added Columns",{{"vsLM", Percentage.Type}, {"vsLY", Percentage.Type}, {"vsLYDec", Percentage.Type}})
        
    
    in
        #"Changed Type1"

     

     

     

1 Reply

  • Here is one way of doing it:

     

     

     

    let
    
    //these three lines are just reading in the data
    //replace them with whatever you use before you start the adding row and columns stuff
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY5LDoMhCISv0rhmwUuUZdNjmP/+16g69ABNTL4BBpy12qdRUzKfl56gml9G9UcK6IZ+5GU6fHN2kBm+GZgXQwfmHft9KJjHb9TF2kOrvXclQmxa4t4/op8Awjtp/40mhERARAgEUhzPGNhydJgkjzkr8G4wR1nMICbX4dD7+U6tedO9dvn3e54v", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RawData = _t, #"202011" = _t, #"202012" = _t, #"202101" = _t, #"202102" = _t, #"202103" = _t, #"202104" = _t, #"202105" = _t, #"202106" = _t, #"202107" = _t, #"202108" = _t, #"202109" = _t, #"202110" = _t, #"202111" = _t, #"202112" = _t, #"202201" = _t, #"202202" = _t, #"202203" = _t]),
        #"Remove Last Row" =  Table.RemoveLastN(Source,1),
        #"Previous Step" = Table.TransformColumnTypes(#"Remove Last Row", List.Transform(List.RemoveFirstN(Table.ColumnNames(#"Remove Last Row"),1),
            each {_, Int64.Type})),
    
    
    //calculate DO Row
      // Change #"Previous Step" to whatever it really is in your code
        cRow = List.RemoveLastN(List.Reverse(Record.ToList(#"Previous Step"{0})),1),
        aRow = List.RemoveLastN(List.Reverse(Record.ToList(#"Previous Step"{1})),1),
    
        doRowRev = List.Generate(
            ()=>[do=List.Sum(List.Range(List.Alternate(aRow,2,1,1),0,5)) / 0.02 /
                     List.Sum(List.Range(cRow,0,12)), 
                  idx=0],
            each [idx] < List.Count(aRow)-12,
            each [do=List.Sum(List.Range(List.Alternate(List.RemoveFirstN(aRow,[idx]+1),2,1,1),0,5)) / 0.02 /
                        List.Sum(List.Range(cRow,[idx]+1,12))
            
            , idx=[idx]+1],
            each [do]
                        ),
    
    //add DO Row to table
        //add enough nulls at the end + the Row label
        addNulls = Record.FromList(List.InsertRange(List.Reverse(doRowRev),0,{"DO"} & 
            List.Repeat({null},Table.ColumnCount(#"Previous Step")-List.Count(doRowRev)-1 )), Table.ColumnNames(#"Previous Step")),
        addDO = Table.FromRecords(Table.ToRecords(#"Previous Step") & {addNulls}),
    
    //set data types
    //   I set as number but you could use integer if you prefer, as shown in your screen shot
        typeIt=Table.TransformColumnTypes(addDO, {{"RawData", type text}} & List.Transform(
            List.RemoveFirstN(Table.ColumnNames(addDO),1), each {_, type nullable number}
        )),
    
    //add the extra columns
        thisMonth = List.Last(Table.ToColumns(typeIt)),
        lastYear = List.Last(List.RemoveLastN(Table.ToColumns(typeIt),12)),
        lastMonth = List.Last(List.RemoveLastN(Table.ToColumns(typeIt))),
        lastDec = Table.Column(typeIt,Text.From(Number.From(Text.Start(List.Last(Table.ColumnNames(typeIt)),4))-1) & "12"),
        newCols = List.Generate(
            ()=>[lm=(thisMonth{0}-lastMonth{0})/lastMonth{0}, 
                ly = (thisMonth{0}-lastYear{0})/lastYear{0},
                ld = (thisMonth{0}-lastDec{0})/lastDec{0},
                idx = 0],
            each [idx]<3,
            each [lm=(thisMonth{[idx]+1}-lastMonth{[idx]+1})/lastMonth{[idx]+1}, 
                ly = (thisMonth{[idx]+1}-lastYear{[idx]+1})/lastYear{[idx]+1},
                ld = (thisMonth{[idx]+1}-lastDec{[idx]+1})/lastDec{[idx]+1},
                idx = [idx]+1],
            each Record.FromList({[lm],[ly],[ld]},{"vsLM","vlLY","vsLYDec"})
            ),
    
        #"Added Columns" = Table.FromColumns(Table.ToColumns(typeIt) & Table.ToColumns(Table.FromRecords(newCols)),
            Table.ColumnNames(Source) & {"vsLM","vsLY","vsLYDec"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Added Columns",{{"vsLM", Percentage.Type}, {"vsLY", Percentage.Type}, {"vsLYDec", Percentage.Type}})
        
    
    in
        #"Changed Type1"