Forum Discussion
Sum from different table
Good day all,
Today i am once again having an 'issue' with powerbi.
I'm creating a report with numerous measures, but i'm wanting to put most of those measures in the Power Query editor. In this case, i have 2 tables. where one is a fact table and the other a dimension table, of sorts. (not entirely true)
Now in my facts table i have a couple columns, there is a unique id called 'workorder' and each workorder can have a 1 to many relation to a ticket number. The ticket number in this table is not unique.
The dimension table has a couple of columns, and one is the ticket number. In this table it is unique.
So far so good.
In the workorder (facts) table i have time calculations that measure the pause time, throughput time and all that of a workorder. And these i want to sum up in a corresponding column in the ticket (dimension) table. In dax terms this would be a one to many relation.
I know for fact that in DAX i can calculate this without too much issues. (something like, calculate(sum(table1:columna);table1:workorderid=table2:workorderid2))
But is this possible in powerquery? Cause i do not seem to be able to pull columns from another table there, or simply do not know how to.
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"
13 Replies
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)
i have an absolute dislike about the logic these fora have, as there is no logical order in the which the responses are listed.
But, thanks to Anonymous i have found that grouping the table works for the need i have, after this i simply take the max and min values that i need in the aggegration, and the rest which IS unique i can just add with the 'all columns' aggegration.
So thank you!
