Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
RawData | 202011 | 202012 | 202101 | 202102 | 202103 | 202104 | 202105 | 202106 | 202107 | 202108 | 202109 | 202110 | 202111 | 202112 | 202201 | 202202 | 202203 |
C | 2,348 | 2,498 | 2,234 | 2,648 | 2,791 | 2,743 | 2,669 | 2,944 | 2,885 | 2,800 | 2,786 | 2,986 | 2,627 | 2,853 | 2,572 | 2,590 | 3,513 |
A | 11,032 | 11,044 | 11,051 | 10,345 | 11,048 | 11,166 | 11,661 | 11,800 | 11,077 | 10,400 | 10,196 | 9,885 | 10,006 | 10,433 | 10,808 | 11,624 | 12,829 |
This is the table output,
FinalData | 202011 | 202012 | 202101 | 202102 | 202103 | 202104 | 202105 | 202106 | 202107 | 202108 | 202109 | 202110 | 202111 | 202112 | 202201 | 202202 | 202203 | vsLM | vsLY | vsLYDec |
C | 2,348 | 2,498 | 2,234 | 2,648 | 2,791 | 2,743 | 2,669 | 2,944 | 2,885 | 2,800 | 2,786 | 2,986 | 2,627 | 2,853 | 2,572 | 2,590 | 3,513 | 36% | 26% | 23% |
A | 11,032 | 11,044 | 11,051 | 10,345 | 11,048 | 11,166 | 11,661 | 11,800 | 11,077 | 10,400 | 10,196 | 9,885 | 10,006 | 10,433 | 10,808 | 11,624 | 12,829 | 10% | 16% | 23% |
DO | #REF! | #REF! | #VALUE! | #REF! | #REF! | #VALUE! | #REF! | #REF! | #VALUE! | #REF! | #REF! | #VALUE! | 82 | 83 | 81 | 81 | 83 | 2% | #REF! | 0% |
Solved! Go to Solution.
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"
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |