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

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