Forum Discussion

j1s's avatar
j1s
Helper I
1 year ago
Solved

Merging / appending to keep all

I have 2 tables containing the number of times people attended per week that look like this:   Table 1: Full Name 2024-11-25 2024-12-02 2024-12-09 2024-12-16 Name 1       ...
Need Help
  • dufoq3's avatar
    dufoq3
    1 year ago

    Hi j1s, I recommend Table.Join function for this purpose:

     

    Output

    let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVUwVNJRgiNDpVgdqLgRTARMGSEkjJE1IIRNoIoNMUwyxWWSGaoWhIQ5disskNQboshYYtdgaIBitVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Full Name" = _t, #"2024-11-25" = _t, #"2024-12-02" = _t, #"2024-12-09" = _t, #"2024-12-16" = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVUwVNJRAiI4FasDlTCCiBhDJY0QMsYQGQwdJkiCQAoubooQRJUwQ7UCyShzhAyq3RaoZiFpscSlxdAApx5DCwOcuiwMMbTFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Full Name" = _t, #"2025-01-01" = _t, #"2025-01-08" = _t, #"2025-01-15" = _t, #"2025-01-22" = _t]),
    T2_RenamedColumn = Table.RenameColumns(Table2,{{"Full Name", "Full Name2"}}),
    Merged = Table.Join(Table1, "Full Name", T2_RenamedColumn, "Full Name2", JoinKind.FullOuter),
    ReplacedFullName = Table.ReplaceValue(Merged, 
        each [Full Name] is null,
        each [Full Name2],
        (x,y,z)=> if y then z else x,
        {"Full Name"} ),
    RemovedColumns = Table.RemoveColumns(ReplacedFullName,{"Full Name2"})
in
    RemovedColumns
  • PwerQueryKees's avatar
    PwerQueryKees
    1 year ago

    Sorting the columns is not that easy, because your column names are text and we need a date sort.

    But this works:

    let
    #"Unpivoted Table1" = Table.UnpivotOtherColumns(Table1, {"Full Name"}, "Attribute", "Value"),
    #"Unpivoted Table2" = Table.UnpivotOtherColumns(Table2, {"Full Name"}, "Attribute", "Value"),
    #"Unpivoted Table3" = Table.UnpivotOtherColumns(Table3, {"Full Name"}, "Attribute", "Value"),
    #"Unpivoted ALL"= Table.Combine({#"Unpivoted Table1",#"Unpivoted Table2",#"Unpivoted Table3"}),
    #"Added Custom" = Table.AddColumn(#"Unpivoted ALL", "Dates", each [Attribute]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Dates", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Attribute", "Dates"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Dates", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted ALL", List.Distinct(#"Unpivoted ALL"[Attribute]), "Attribute", "Value", List.Sum),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",#"Sorted Rows"[Attribute])
in
    #"Reordered Columns"

    Producing this:

     

    About the missing dates: I suspect this will not be a problem in real life.

     

    But if it is:

    • create a table with all possible dates, for exapmple by querying the dat range in the orignal dataset
    • Transpose the table
    • Use Forst Row as Headers
    • And if needed, remove all rows with a Table.SelectRows(#"Promote Headers", each false)
    • and then do a Table.Union of this table with the pivoted table

    I doubt this will be worth the trouble ....

    Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
    Remember: You can mark multiple answers as a solution...
    If I helped you, please click on the Thumbs Up to give Kudos.

    Kees Stolker

    A big fan of Power Query and Excel

PwerQueryKees's avatar
PwerQueryKees
Super User
1 year ago

The trick is to first unpivot, then combine the 3 unpivoted tables and the pivot again.

 

From this

Using

let
    #"Unpivoted Table1" = Table.UnpivotOtherColumns(Table1, {"Full Name"}, "Attribute", "Value"),
    #"Unpivoted Table2" = Table.UnpivotOtherColumns(Table2, {"Full Name"}, "Attribute", "Value"),
    #"Unpivoted Table3" = Table.UnpivotOtherColumns(Table3, {"Full Name"}, "Attribute", "Value"),
    #"Unpivoted ALL"= Table.Combine({#"Unpivoted Table1",#"Unpivoted Table2",#"Unpivoted Table3"}),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted ALL", List.Distinct(#"Unpivoted ALL"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"

to this:



Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

  • j1s's avatar
    j1s
    Helper I
    1 year ago

    Thanks, PwerQueryKees , Omid_Motamedise . 

     

    That's helpful. Using this method, how do I fix a couple of problems?:
    1. it seems to ditch columns with no entries

    2.  the dates columns are not in sequence across the top (in the headers)

     

    There seems to be an althernative (less efficient) 2 query method that does preserve the date sequence:
    Query A (a helper query) that merges Table1 and Table 2 using right anti join 

     

    let
    Source = Table.NestedJoin(Table1, {"Full Name"}, Table2, {"Full Name"}, "Table2", JoinKind.RightAnti),
    Table3 = Source{0}[Table2]
in
    Table3

     

    Query B (a final query) that first merges Table1 and Table 2 using left outer join, then appends the data from Query A 

     

        Source = Table.NestedJoin(Table1, {"Full Name"}, Table2, {"Full Name"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"01/01/2025", "08/01/2025", "15/01/2025", "22/01/2025"}, {"01/01/2025", "08/01/2025", "15/01/2025", "22/01/2025"}),
    #"Appended Query" = Table.Combine({#"Expanded Table2", #"Query A (helper)"})
in
    #"Appended Query"

     

     

    This produces the correct output:

     

    I would prefer to do it all in one query using the unpivot method if I can understand how to make the dates columns appear in sequence  

     

      

    • PwerQueryKees's avatar
      PwerQueryKees
      Super User
      1 year ago

      Sorting the columns is not that easy, because your column names are text and we need a date sort.

      But this works:

      let
    #"Unpivoted Table1" = Table.UnpivotOtherColumns(Table1, {"Full Name"}, "Attribute", "Value"),
    #"Unpivoted Table2" = Table.UnpivotOtherColumns(Table2, {"Full Name"}, "Attribute", "Value"),
    #"Unpivoted Table3" = Table.UnpivotOtherColumns(Table3, {"Full Name"}, "Attribute", "Value"),
    #"Unpivoted ALL"= Table.Combine({#"Unpivoted Table1",#"Unpivoted Table2",#"Unpivoted Table3"}),
    #"Added Custom" = Table.AddColumn(#"Unpivoted ALL", "Dates", each [Attribute]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Dates", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Attribute", "Dates"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Dates", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted ALL", List.Distinct(#"Unpivoted ALL"[Attribute]), "Attribute", "Value", List.Sum),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",#"Sorted Rows"[Attribute])
in
    #"Reordered Columns"

      Producing this:

       

      About the missing dates: I suspect this will not be a problem in real life.

       

      But if it is:

      • create a table with all possible dates, for exapmple by querying the dat range in the orignal dataset
      • Transpose the table
      • Use Forst Row as Headers
      • And if needed, remove all rows with a Table.SelectRows(#"Promote Headers", each false)
      • and then do a Table.Union of this table with the pivoted table

      I doubt this will be worth the trouble ....

      Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
      Remember: You can mark multiple answers as a solution...
      If I helped you, please click on the Thumbs Up to give Kudos.

      Kees Stolker

      A big fan of Power Query and Excel

      • j1s's avatar
        j1s
        Helper I
        1 year ago

        Could you explain the adding of a custom column and then grouping, rather than say changing the Attributes column to type date, then sorting and then chnaging it back to text again?