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"

     

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

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.