Forum Discussion

decarsul's avatar
decarsul
Helper 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 t...
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"

     

decarsul's avatar
decarsul
Helper V
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    decarsul
    Helper V
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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

  • decarsul's avatar
    decarsul
    Helper V
    5 years ago

    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.