Forum Discussion
Anonymous
4 years agoNot applicable
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...
- 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"
ronrsnfld
4 years agoSuper User
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"