Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear, I have the following table where an invoice can have many "Register Numbers":
Register_Number | Invoice | Value | DateHour |
14500 | E001-00605 | 1000 | 8/02/2020 10:00 |
14600 | E001-00605 | -1000 | 8/02/2020 16:00 |
14700 | E001-00605 | 1000 | 8/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_Number | Invoice | Value | DateHour |
14700 | E001-00605 | 1000 | 8/02/2020 16:10 |
I don't want to use DAX because I'd have to create a new table.
Regards
Joao
Solved! Go to Solution.
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
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:
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:
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |