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"
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?
I hear ya,
basically i want to keep all columns and simply add extra's. (in your code example, it would remove the original individual values and replace them with unique summed values, but the empty column is removed entirely)
I want to be able to report on both the work order as the ticket table, as workorders are subsections of the tickets, they only show a part of the full story, but they can show a full story depending on who handled the workorder. So you could see the workorder level as a quality monitoring on employee basis, where as the ticket level is on customer basis and shows their entire audit trail.
Right now, most logic is just the plain value.
Timestamp12 and timestamp13 are export values, so in this case non calculated columns.
But my actual files has about 20 columns, of which 8 are calculated.
Eventually, i want to be able to report on customer level (ticket) to see what the gross and net throughput time was. Gross as in, that is what the customer has experienced, net is what will be how much influence one department has (depending on which department or employee that is can be defined via filters) had on the entire ticket. See if there is quality and time issue that one department (workorder) has compared to the next at a same workload for example.
Now there is also the pause times which i mentioned earlier. This is going to be an if argument. Sometimes a department can't do anything about a workorder and they 'close' it with an exception code, based on this exception code (if indeed it was not ment for them) you will want to deduct this time frm the gross throughput time of the entire ticket, resulting in a net throughput time, or time that the ticket has actually been worked on.
This is just a fraction of what we're going to be doing with it.
You can work on the ticket table by adding calculated columns as follows. But if your tables are very large it is preferable to use table.group and then join.
let Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY09C8MwDET/yuG5Bkuy+7F3CV27GVMymNLBbaid/1+R4DTDwYN7OsVo7rfhivYqubaxTMQ7Fjy/n1pR5/L40zTONS/0zg0akw7R6KGQJbbs6ALylpTYgQKIIDjDr56H9O7YQcBwGlqVgNAbdvshQdAhXixW2KztHevISff0TTAp/QA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t]), #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Colonna1", type text}}), #"Suddividi colonna in base al delimitatore" = Table.SplitColumn(#"Modificato tipo", "Colonna1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Colonna1.1", "Colonna1.2", "Colonna1.3", "Colonna1.4", "Colonna1.5", "Colonna1.6", "Colonna1.7", "Colonna1.8"}), #"Intestazioni alzate di livello" = Table.PromoteHeaders(#"Suddividi colonna in base al delimitatore", [PromoteAllScalars=true]), #"Rimosse colonne" = Table.RemoveColumns(#"Intestazioni alzate di livello",{"sum_gross", "sum_pause", "sum_net"}), #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Rimosse colonne", "sum_trhu", each List.Sum(Table.SelectRows(workOrder, (r)=>r[TKID]=[TKID])[Gross_thru])) in #"Aggiunta colonna personalizzata"I'll give it a go. However it sounds a bit counter productive to start with a large dataset, do the calculated columns, reduce the size via grouping to re-join part of the same data later.
But hey, if it works? 😛
I did not say to do all this together: they are alternative ways.
One uses table.addcolumn, the other uses table.group and table.join
So i tested this out, it works . . .
but while testing it, i found you can add another aggegration rule, which isn't an aggegration at all. The lowest option stating 'all rows' is actually like a join, where you can select the columns that you wish to keep. Or atleast, this is how i'm seeing it.
I am getting duplicate items returned now tho, but that makes sence as appearantly my data is not clean, so its returning every unique row.