Sum from different table

[decarsul]

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.

[decarsul]

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!

[decarsul]

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 . . . )

[Anonymous · Accepted solution]

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"

[decarsul]

Or . . . can i add my custom calculated formula's instead of the default aggegrations?

[Anonymous]

I think so. but if you want help, write the custom formula you want and I'll try.

[decarsul]

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)

[Anonymous]

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"

[decarsul]

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.

[decarsul]

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? 😛

[Anonymous]

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

[Anonymous]

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?

[decarsul]

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.

[Anonymous]

sure it's possible. and, if you upload sample tables that are easily copied, you will get several helpful answers from multiple people.