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.
let's say I have a value of 10 on the date 2019-04-12 and value 12 on the date of 2019-04-22, that is two columns and two rows table like:
Date, Value
2019-04-12, 10
2019-04-22, 12
I need to make the date consecutive between the start date of April 12 and today (12, 13, 14, ..., 22, ..., 26), say today is April 26, and I want the days that lack of values takes the last known value, in this case it should fill the value of 10 for each empty day between 13 and 21 and fills with value of 12 for each empty empty day between 23 and today (April 26). NOT in DAX but in M query, I have reason to do so. Can anyone help me with the technique? Sincerely /Dav
Solved! Go to Solution.
@Anonymous can you take care of those or you need assistance, there is nothing much to do to bring those columns
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous here it is
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ1MNE1NFLSUTI0ABKOjoZA0skJRDo7GyrF6iBUGYFUGUFUGYFVGYFVGSnFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t, #"Attribute A" = _t, #"Attribute B" = _t, #"Attribute C" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter), #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Date"}, {"Date.1"}), #"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "End Date", each (if [Date.1] = null then Date.From(DateTime.LocalNow()) else Date.AddDays([Date.1],-1)), type date), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Date.1"}), #"Added Custom1" = Table.AddColumn(#"Removed Columns", "New Date", each {Number.From([Date])..Number.From([End Date])}), #"Expanded New Date" = Table.ExpandListColumn(#"Added Custom1", "New Date"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded New Date",{{"New Date", type date}}), #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"End Date", "Date"}) in #"Removed Columns1"
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |