Forum Discussion
Sum from different table
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"
Allright, fair enough. Was hoping the text was sufficient enough.
Here a short list of the makeup, completely fictional data.
We start with the workorder table, that already exists and the last 3 columns are calculated.
Workorder table
TKID;WOID;timestamp 1;timestamp2;Gross thru;Pause time;Net thru
12;32131;1-1-2020;3-1-2020;2;1;1
14;321654;4-1-2020;6-1-2020;2;0;2
15;65496;5-1-2020;18-1-2020;13;5;8
28;65465;2-1-2020;19-1-2020;17;3;14
12;1568;4-1-2020;13-1-2020;9;2;7
Than the ticket table, where the sum's are the result of the above workorder table. Ofcourse right now, manually filled in. The gross and net are calculated columns based on the data that is in the ticket table.
Ticket table
TKID;timestamp 12;timestamp 13;gross;sum gross;sum pause;sum net;net
12;31-12-2019;14-1-2020;15;11;3;8;4
14;3-1-2020;6-1-2020;3;2;0;2;1
15;5-1-2020;20-1-2020;15;13;5;8;2
28;2-1-2020;24-1-2020;22;17;3;14;5
(fora wouldn't allow me to paste the tables . . . )
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?
Or . . . can i add my custom calculated formula's instead of the default aggegrations?
I think so. but if you want help, write the custom formula you want and I'll try.