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.
Meus amigos, tenho um banco de dados no excel onde tenho 3 colunas. A com código de veículo. B com data de chegada em uma cidade. C com diferença em dias, calculada com a última chegada me uma cidade e o penultima. este cálculo se repete várias vezes por vários dias com base no id do veículo, como faço no power bi, seja dax ou M.
Duration type things are good to do in PoweQuery in my opinion. I have attached the file below, but here's the general steps:
PQ code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkvNyEzOSVXSUXJJLElVitWBCykYAgUN9Q31jQwMLVEkjCASBpgyxmAZYyx6QIYZ6RvjNMwIu2EgYewyJvowLbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Vehicle", type text}, {"Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type1", {"Vehicle"}, {{"Data", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Sort( [Data],{"Date",Order.Ascending})), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.AddIndexColumn( [Custom],"Index1", 1,1)), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Table.AddIndexColumn( [Custom.1], "Index0", 0,1)), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each Table.NestedJoin( [Custom.2],{"Index0"}, [Custom.2],{"Index1"}, "Join", JoinKind.LeftOuter )), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.4", each Table.ExpandTableColumn([Custom.3], "Join", {"Date"}, {"Prev Date"})), #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Custom.5", each Table.AddColumn( [Custom.4], "Days", each Duration.Days( [Date] - [Prev Date]))), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom5",{"Custom.5"}), #"Expanded Custom.5" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.5", {"Vehicle", "Date", "Days"}, {"Vehicle", "Date", "Days"}), #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom.5",{{"Vehicle", type text}, {"Date", type date}, {"Days", Int64.Type}}) in #"Changed Type2"
File:
Nick, good morning. Sorry for the time. First of all I would like to thank you very much for your help and your willingness, thank you very much.
Another doubt, I saw that in the dax that you developed, it generates a kind of consolation.
Is it possible for days between one date and another to be displayed on a daily basis?
Why do I need the information open per vehicle based on the day? I am sending two images, one shows the data open,
the other shows the purpose (purpose) of the numbers.
as it is in the image, thank you again and ask for the disturbance.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |