cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davzh Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User
Super User

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

@davzh can you take care of those or you need assistance, there is nothing much to do to bring those columns






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





Super User
Super User

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

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

 

 

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





10 REPLIES 10
Super User
Super User

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

@ImkeF 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

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

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

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





davzh Regular Visitor
Regular Visitor

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

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

 

Super User
Super User

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

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






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





davzh Regular Visitor
Regular Visitor

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

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.

Smiley Happy

Thanks in advance.

/Dav

Super User
Super User

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

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





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





davzh Regular Visitor
Regular Visitor

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

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

Smiley Happy))

/Dav

Super User
Super User

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

@davzh can you take care of those or you need assistance, there is nothing much to do to bring those columns






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





Super User
Super User

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

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

 

 

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 415 members 4,198 guests
Please welcome our newest community members: