Forum Discussion

decarsul's avatar
decarsul
Icon for Helper V rankHelper V
5 years ago
Solved

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.

Need Help
  • Anonymous's avatar
    Anonymous
    5 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"