Forum Discussion
Sum from different table
- Anonymous5 years ago
try this for now, then we'll fix the shot
let Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY7NCoMwEIRfZchZwd38GO+FUgptD4UegoiHQHsQi4nv3ygk9rDLMPPBjHPieb2c8LqnFz+TD3GcvnRIxnmZQxjie1nxGNfghy3DzcfdE5UQfeUEMSSTJFBNNTfcQGbBSG7h1MYZraBybg4wXQE1EtUZ6ByTLUpCw2aS7UYaDS55V1QLCVJ/K0kbe3RTWdml+nbn+h8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t, Colonna2 = _t]), #"Suddividi colonna in base al delimitatore" = Table.SplitColumn(Origine, "Colonna1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Colonna1.1", "Colonna1.2", "Colonna1.3", "Colonna1.4", "Colonna1.5", "Colonna1.6", "Colonna1.7"}), #"Modificato tipo" = Table.TransformColumnTypes(#"Suddividi colonna in base al delimitatore",{{"Colonna1.1", type text}, {"Colonna1.2", type text}, {"Colonna1.3", type text}, {"Colonna1.4", type text}, {"Colonna1.5", type text}, {"Colonna1.6", type text}, {"Colonna1.7", type text}, {"Colonna2", type text}}), #"Intestazioni alzate di livello" = Table.PromoteHeaders(#"Modificato tipo", [PromoteAllScalars=true]), #"Modificato tipo1" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"TKID", Int64.Type}, {"WOID", Int64.Type}, {"timestamp1", type date}, {"timestamp2", type date}, {"Gross_thru", Int64.Type}, {"Pause_time", Int64.Type}, {"Net_thru", Int64.Type}, {"", type text}}), #"Raggruppate righe" = Table.Group(#"Modificato tipo1", {"TKID"}, {{"sumGross", each List.Sum([Gross_thru]), type nullable number}, {"sumPause", each List.Sum([Pause_time]), type nullable number}, {"sumThru", each List.Sum([Net_thru]), type nullable number}}) in #"Raggruppate righe"
Right, had some issues pasting that, powerbi added quote's to oblivion.
Anyhow, got it working.
So you made a 'grouped' table from the original data. Where the grouping becomes the unique id (as you basically remove duplicates) and sum the rest (in this instance).
This looks promising. Now i have more columns that need to be printed, as they contain data that is unique to that ticket id (timestamp 12 and timestamp 13). How would i keep those? (so i don't have to join them back in)
It is easier to show it to you in an example than to explain how to do it.
Which columns do you want to keep?
With what logic?
I saw that you have Timestamp12 and timestamp13. How are they calculated or how are they chosen?