Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Table Summarize from another Table

Hi, Good day! anyone could assist me with how to have it on a power query. I would like to fetch the group results value that came from other tables and update value base from table header.   Thi...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi Anonymous ,

     

    Paste this into a new blank query in Advanced Editor to see the steps I took:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc1LCsAwCATQu7hOqI6mn2XJMST3v0ZDMWALgjI8RncSKiTQes/Nss0B4w2ZaRQnZNASQACd93FetUcDFrAAloBwAquhZWAJaID990I/DeMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"#" = _t, #"PO No" = _t, Date = _t, Value = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"#", Int64.Type}, {"PO No", type text}, {"Date", type date}, {"Value", Int64.Type}}),
    addColumnNames = Table.AddColumn(chgTypes, "columnNames", each Text.Start(Date.MonthName([Date]), 3) & " Achieved"),
    groupRows = Table.Group(addColumnNames, {"PO No", "columnNames"}, {{"value", each List.Sum([Value]), type nullable number}}),
    pivotColumnNames = Table.Pivot(groupRows, List.Distinct(groupRows[columnNames]), "columnNames", "value", List.Sum)
in
    pivotColumnNames

     

    Using your example data, I get this ouput:

     

    Pete

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks Pete.

 

I think it works fine, but i have some date that doesn't have dates, that's why i received some error "We cannot convert the value null to type Text."
how can i able to disregards the null value from the date column.

 

thanks again.

BA_Pete's avatar
BA_Pete
Super User
4 years ago

Hi Anonymous ,

 

It depends on what you want to do with these null rows.

If they have values that need to be allocated, but no date, you might want to swap in a 'dummy' date so you retain the values.

If not, then you would just filter out rows where [Date] = null before doing the steps in my code.

 

Pete