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...
  • ronrsnfld's avatar
    4 years ago

    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"