Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Is there similar LastNonBlank type of function in M- query, or likely give the same result?

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

 

2 ACCEPTED SOLUTIONS

@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.

View solution in original post

@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"

 

 

 

image.png

 

 

 

 



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.

View solution in original post

10 REPLIES 10
Greg_Deckler
Super User
Super User

@ImkeF 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Anonymous try this. 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ1MNE1NFLSUTI0UIrVQYgZgcSMgGKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "New Date", each {Number.From([Date])..Number.From(Date.From(DateTime.LocalNow()))
}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "New Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"New Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date"})
in
    #"Removed 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
Not applicable

Hello Parry,

you are almost there, just a little problem, NewDate.JPG

as you see, the date bewteen 22nd and 26th with value 10 should not be there.

Sincerely

/Dav

 

@Anonymous aha oversight on my side. quick question, are there always going to be two rows of dates?



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
Not applicable

Hello again,

No, it can be a many and any rows data table, and it can grown very largely, and can have any kinds of date and value combination. This is just an example, I shall scale it up later.

Make things more complex, there will be serveral more columns beside the date and value, say an attributeA column: row 1 = A1, row 2 = A2; and an attributeB column: row 1 = B1, row 2 = B2.... .

I will say good night now, and I am looking forward to check your answer tomorrow morning.

🙂

Thanks in advance.

/Dav

@Anonymous let's check if basic result is working, check following code

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ1MNE1NFLSUTI0UIrVQYgZgcSMgGKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _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.

Anonymous
Not applicable

Yes, it works! I just would like to have several columns besides these 2, such as AttributeA, AttributeB and AttributeC, then it is done!

:)))

/Dav

@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
Not applicable

Thanks Parry very much, I think I can add a few columns besides myself, otherwise I come back to you.

It is a great help. I appreciate it!

🙂

/Dav

@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"

 

 

 

image.png

 

 

 

 



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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.