Forum Discussion
vpatel55
Kudo Commander
Kudo CommanderConverting from months to days in Power Query for every category
We have a monthly budget which I'm looking to convert to a daily value budget. This is by dividing the monthly budget by the days in the month. It needs to be done in Power Query (not DAX). Howev...
Hi vpatel55 ,
Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDRNzIwMlLSUXIsKMhJLQYyLAwtlWJ1ILKmGLJmBoZwWTMMWRMjY7gs3OSA1MQinAbDJLGaC5PEaqx/UWJeOm4XI6SxGo2QBhseCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Fruit = _t, Count = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Fruit", type text}, {"Count", Int64.Type}}), #"Added DailyCount" = Table.AddColumn(#"Changed Type", "DailyCount", each [Count] / Date.DaysInMonth([Month])), #"Added DaysMonth" = Table.AddColumn(#"Added DailyCount", "DaysMonth", each List.Dates([Month], Date.DaysInMonth([Month]), #duration(1,0,0,0))), #"Expanded DaysMonth" = Table.ExpandListColumn(#"Added DaysMonth", "DaysMonth") in #"Expanded DaysMonth"
latimeria
Solution Specialist
Solution SpecialistHi vpatel55 ,
Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDRNzIwMlLSUXIsKMhJLQYyLAwtlWJ1ILKmGLJmBoZwWTMMWRMjY7gs3OSA1MQinAbDJLGaC5PEaqx/UWJeOm4XI6SxGo2QBhseCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Fruit = _t, Count = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Fruit", type text}, {"Count", Int64.Type}}),
#"Added DailyCount" = Table.AddColumn(#"Changed Type", "DailyCount", each [Count] / Date.DaysInMonth([Month])),
#"Added DaysMonth" = Table.AddColumn(#"Added DailyCount", "DaysMonth", each List.Dates([Month], Date.DaysInMonth([Month]), #duration(1,0,0,0))),
#"Expanded DaysMonth" = Table.ExpandListColumn(#"Added DaysMonth", "DaysMonth")
in
#"Expanded DaysMonth"