Forum Discussion
Solution SageCalculate data for every week in matrix
Solution SageHI ValtteriN ,
your knowledge of power query is excellent but,
It is difficult for me to understand how I should implement this, the problem is that I take data from the OLAP system.
In addition to the date, I have budget items
My complete tables contain the following data
| Data.Year | Data.Mounth | id organization | id. unit | Article budget | Budget |
| 2021 | 01.01.2021 | 123 | 321 | Article 1 | 120000 |
| 2021 | 01.01.2021 | 123 | 321 | Article 2 | 100000 |
I understand that the Request has to convert my table.
Community ChampionHi,
Here is how you can chage that kind of data to daily budget:
Example:
Steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQz1DMyMDJU0lEyNAABIMOzJDXXUClWB1XWCEnWSCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Budget = _t, Item = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Budget", Int64.Type}}),
//Add amount of months
#"MonthlistS" = Table.AddColumn(#"Changed Type", "Monthlist", each List.Numbers(1,12)), //create list
#"months2"= Table.ExpandListColumn(#"MonthlistS", "Monthlist"), //Here I fill the yearly budget to every month
#"Divided Column" = Table.TransformColumns(months2, {{"Budget", each _ / 12, type number}}), //Since now it is on a monthly level. I divide the budget with 12
#"NewDate1"= Table.AddColumn(#"Divided Column", "Newdate1", each #date(Date.Year([Date]),[Monthlist],1)as date), //Create date from month
#"Changed Type2" = Table.TransformColumnTypes(NewDate1,{{"Newdate1", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type2", "DaysInMonth", each Date.DaysInMonth([Newdate1])), //Here I add the amount of days for every month
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "DateList", each List.Numbers(1,[DaysInMonth])), //Now I make a list of values based on previous step
#"Expanded DateList" = Table.ExpandListColumn(#"Added Custom2", "DateList"), //Here the list is expanded
#"Added Custom3" = Table.AddColumn(#"Expanded DateList", "DailySum", each [Budget]/[DaysInMonth]), //Now we transform monthly budget to a daily one
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Newdate", each #date(Date.Year([Date]),Date.Month([Newdate1]),[DateList])as date), //Here I add a proper Date column
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom4",{{"DailySum", type number}, {"Newdate", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date", "Budget", "Monthlist", "Newdate1", "DaysInMonth", "DateList"}) // Some data type changes and clean up
in
#"Removed Columns"
End result: