Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Power query for column caculation

Hi,  

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

yalesg_0-1652348618192.png

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)  

yalesg_1-1652348819872.png

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

yalesg_2-1652349317029.png

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%
1 ACCEPTED SOLUTION
ronrsnfld
Super User
Super User

Here is one way of doing it:

ronrsnfld_0-1652369079729.png

 

 

 

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"

 

 

 

View solution in original post

1 REPLY 1
ronrsnfld
Super User
Super User

Here is one way of doing it:

ronrsnfld_0-1652369079729.png

 

 

 

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"

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors