cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JoaoMS Helper I
Helper I

Filter rows by last update in Query Editor

Dear, I have the following table where an invoice can have many "Register Numbers":

 

Register_NumberInvoiceValueDateHour
14500E001-0060510008/02/2020 10:00
14600E001-00605-10008/02/2020 16:00
14700E001-0060510008/02/2020 16:10

 

What I want to filter from Query Editor is just the last version of the Registered Invoice based either on the "DateHour" column or the "Register_Number" column.

 

The output table should be:

 

Register_NumberInvoiceValueDateHour
14700E001-0060510008/02/2020 16:10

 

I don't want to use DAX because I'd have to create a new table.

 

Regards

Joao

2 ACCEPTED SOLUTIONS

Accepted Solutions
camargos88 Community Champion
Community Champion

Re: Filter rows by last update in Query Editor

Hi @JoaoMS ,

 

You can try this code:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQxNTBQ0lFyNTAw1DUwMDMwBXIMDcBiFvoGRvpGBkYGCoYGVkCRWB2QejNM9bqYGswQGsyJscDMyhCoPhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Register_Number = _t, Invoice = _t, Value = _t, DateHour = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Register_Number", Int64.Type}, {"Invoice", type text}, {"Value", Int64.Type}, {"DateHour", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Max(#"Changed Type"[DateHour])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Max_Value", each if [DateHour] = [Custom] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Max_Value] = 1))
in
#"Filtered Rows"

 

Ricardo


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

View solution in original post

Microsoft
Microsoft

Re: Filter rows by last update in Query Editor

Hi @JoaoMS ,

 

Using a M query as below:

 

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQxNTBQ0lFyNTAw1DUwMDMwBXIMDcBiFvoGRvpGBkYGCoYGVkCRWB2QejNM9bqYGswQGsyJscDMyhCoPhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Register_Number = _t, Invoice = _t, Value = _t, DateHour = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Register_Number", Int64.Type}, {"Invoice", type text}, {"Value", Int64.Type}, {"DateHour", type datetime}}),
#"New step" = Table.RemoveColumns(Table.SelectRows(Table.AddColumn(Table.AddColumn(#"Changed Type", "Custom", each List.Max(#"Changed Type"[DateHour])), "Max_Value", each if [DateHour] = [Custom] then 1 else 0), each ([Max_Value] = 1)),{"Custom", "Max_Value"})
in
#"New step"

 

 

Finally you will see:

 

Annotation 2020-03-24 140446.png

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 

 

View solution in original post

2 REPLIES 2
camargos88 Community Champion
Community Champion

Re: Filter rows by last update in Query Editor

Hi @JoaoMS ,

 

You can try this code:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQxNTBQ0lFyNTAw1DUwMDMwBXIMDcBiFvoGRvpGBkYGCoYGVkCRWB2QejNM9bqYGswQGsyJscDMyhCoPhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Register_Number = _t, Invoice = _t, Value = _t, DateHour = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Register_Number", Int64.Type}, {"Invoice", type text}, {"Value", Int64.Type}, {"DateHour", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Max(#"Changed Type"[DateHour])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Max_Value", each if [DateHour] = [Custom] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Max_Value] = 1))
in
#"Filtered Rows"

 

Ricardo


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

View solution in original post

Microsoft
Microsoft

Re: Filter rows by last update in Query Editor

Hi @JoaoMS ,

 

Using a M query as below:

 

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQxNTBQ0lFyNTAw1DUwMDMwBXIMDcBiFvoGRvpGBkYGCoYGVkCRWB2QejNM9bqYGswQGsyJscDMyhCoPhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Register_Number = _t, Invoice = _t, Value = _t, DateHour = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Register_Number", Int64.Type}, {"Invoice", type text}, {"Value", Int64.Type}, {"DateHour", type datetime}}),
#"New step" = Table.RemoveColumns(Table.SelectRows(Table.AddColumn(Table.AddColumn(#"Changed Type", "Custom", each List.Max(#"Changed Type"[DateHour])), "Max_Value", each if [DateHour] = [Custom] then 1 else 0), each ([Max_Value] = 1)),{"Custom", "Max_Value"})
in
#"New step"

 

 

Finally you will see:

 

Annotation 2020-03-24 140446.png

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 

 

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors