Forum Discussion
Pro rata data over previous days
Hi KLS,
I'd say that this is what M/the query-editor is made for. Please find my solution in the last 4 lines of the following code. The first lines are only an optimization of the transformation-steps if your data would actually be badly formatted like they are available now.
If you apply your transformation steps clever, you can harvest some benefits PBI is giving like automatic format-conversion (detection of numbers and dates (without years!!)). Have a look at the video to see how this works.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVYyLC5R8E2sVCjJVzA0KskAsfUUQMDM1EAPrAKizBgiB1ZnjlBnbICiygKhyigvRcGrNC8VpMrQAKwsFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Reading Start Date. Reading End Date. Units Used." = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reading Start Date. Reading End Date. Units Used.", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Reading Start Date. Reading End Date. Units Used.", "Column1"}}), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Column1] <> "")), #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",".","",Replacer.ReplaceText,{"Column1"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","st","",Replacer.ReplaceText,{"Column1"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","th","",Replacer.ReplaceText,{"Column1"}), #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","nd","",Replacer.ReplaceText,{"Column1"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value3","Column1",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true),{"Column1.1", "Column1.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", Int64.Type}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1","Column1.1",Splitter.SplitTextByDelimiter(" to ", QuoteStyle.Csv),{"Column1.1.1", "Column1.1.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1.1", type date}, {"Column1.1.2", type date}}), #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Column1.1.1", "From"}, {"Column1.1.2", "To"}, {"Column1.2", "Value"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Daily", each [Value]/(Number.From([To]-[From])+1)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Dates", each {Number.From([From])..Number.From([To])}), #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom1", "Dates"), #"Changed Type3" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}) in #"Changed Type3"
Thank you smoupre, your transformations on cleaning up data were interesting and useful to learn.
Unless I've missed something the data is still not being split over the previous days. I now have an average daily use but this still shows on just the date of the reading and not all the days before that. Is this something that can be done in Power BI?
Thank you
Hi KLS,
I'd say that this is what M/the query-editor is made for. Please find my solution in the last 4 lines of the following code. The first lines are only an optimization of the transformation-steps if your data would actually be badly formatted like they are available now.
If you apply your transformation steps clever, you can harvest some benefits PBI is giving like automatic format-conversion (detection of numbers and dates (without years!!)). Have a look at the video to see how this works.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVYyLC5R8E2sVCjJVzA0KskAsfUUQMDM1EAPrAKizBgiB1ZnjlBnbICiygKhyigvRcGrNC8VpMrQAKwsFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Reading Start Date. Reading End Date. Units Used." = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Reading Start Date. Reading End Date. Units Used.", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Reading Start Date. Reading End Date. Units Used.", "Column1"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Column1] <> "")),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",".","",Replacer.ReplaceText,{"Column1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","st","",Replacer.ReplaceText,{"Column1"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","th","",Replacer.ReplaceText,{"Column1"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","nd","",Replacer.ReplaceText,{"Column1"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value3","Column1",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true),{"Column1.1", "Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", Int64.Type}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1","Column1.1",Splitter.SplitTextByDelimiter(" to ", QuoteStyle.Csv),{"Column1.1.1", "Column1.1.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1.1", type date}, {"Column1.1.2", type date}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Column1.1.1", "From"}, {"Column1.1.2", "To"}, {"Column1.2", "Value"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Daily", each [Value]/(Number.From([To]-[From])+1)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Dates", each {Number.From([From])..Number.From([To])}),
#"Expanded Dates" = Table.ExpandListColumn(#"Added Custom1", "Dates"),
#"Changed Type3" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}})
in
#"Changed Type3"That is fantastic - thank you so much!