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.
Hello, I've been struggling finding a solution for my problem. I need to add average values between dates in power query, I recieve the readings of a lot of water meters and sometimes we skip like 2 or 3 days, I need to fill those days between with incremental averages so the values are not the same between the days with readings.
Thanks in advance!!!
Solved! Go to Solution.
Hi @Anonymous - you weren't very clear. What is an incremental average? I don't see any blanks in your values above to fill in. However, you can use a pattern similar to this.
You can see there are some dates missing.
The following code fills in those dates:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJS0lEyVorVAQkYwQSMoAIm6CpM0QUsYAKmUAFLJBWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Currency.Type}}),
varStartDate = Number.From(List.Min(#"Changed Type"[Date])),
varEndDate = Number.From(List.Max(#"Changed Type"[Date])),
Custom1 = {varStartDate..varEndDate},
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
#"Changed Type1" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Date"}, #"Changed Type", {"Date"}, "Changed Type1", JoinKind.LeftOuter),
#"Added Values" =
Table.AddColumn(
#"Merged Queries",
"New Value",
each
let
varValue = [Changed Type1][Value],
varDate = [Date],
varRunningTable =
Table.SelectRows(#"Changed Type", each [Date] < varDate),
varAverages = List.Average(varRunningTable[Value])
in
if List.NonNullCount(varValue) = 1 then varValue{0} else varAverages
),
#"Expanded Changed Type1" = Table.ExpandTableColumn(#"Added Values", "Changed Type1", {"Value"}, {"Value"})
in
#"Expanded Changed Type1"
The last column is all you need. The middle column is just there to show you where there are nulls and what was filled in.
If you need more help, you need to give us more info. Usable sample data, expected results, how to calculate them. Please follow the directions below, or if this is enough, then mark it as the solution.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @Anonymous - you weren't very clear. What is an incremental average? I don't see any blanks in your values above to fill in. However, you can use a pattern similar to this.
You can see there are some dates missing.
The following code fills in those dates:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJS0lEyVorVAQkYwQSMoAIm6CpM0QUsYAKmUAFLJBWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Currency.Type}}),
varStartDate = Number.From(List.Min(#"Changed Type"[Date])),
varEndDate = Number.From(List.Max(#"Changed Type"[Date])),
Custom1 = {varStartDate..varEndDate},
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
#"Changed Type1" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Date"}, #"Changed Type", {"Date"}, "Changed Type1", JoinKind.LeftOuter),
#"Added Values" =
Table.AddColumn(
#"Merged Queries",
"New Value",
each
let
varValue = [Changed Type1][Value],
varDate = [Date],
varRunningTable =
Table.SelectRows(#"Changed Type", each [Date] < varDate),
varAverages = List.Average(varRunningTable[Value])
in
if List.NonNullCount(varValue) = 1 then varValue{0} else varAverages
),
#"Expanded Changed Type1" = Table.ExpandTableColumn(#"Added Values", "Changed Type1", {"Value"}, {"Value"})
in
#"Expanded Changed Type1"
The last column is all you need. The middle column is just there to show you where there are nulls and what was filled in.
If you need more help, you need to give us more info. Usable sample data, expected results, how to calculate them. Please follow the directions below, or if this is enough, then mark it as the solution.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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 |
---|---|
101 | |
52 | |
21 | |
12 | |
11 |