none
Dynamically Merge Tables without Key RRS feed

  • Question

  • Hi all,

    I'm hoping for some help on a problem I'm facing.  For a simplified version, I have two tables:


    Warehouses = Table.FromRecords({  
        [Warehouse = "Warehouse A"],  
        [Warehouse = "Warehouse B"]}) 


    2. A list of products:


    Products = Table.FromRecords({  
        [Product = "Product 1"],  
        [Product = "Product 2"]})  


    From these I'd like to generate a new "Stock" table for every Warehouse and Product combintation, where each record includes a "Warehouse", "Product", and a starting "Quantity" of 0.  So from the above tables the result would look like:


    And the query should be able to operate dynamically and account for the addition of new warehouses and/or products.  So for example, if I added 1 more warehouse and 2 more products then the result would be:

    In case it's relevant I'm using 64-bit Excel 2013 on Windows 7 SP1 with Power Query 2.55.

    Any help in getting over this first hurdle would be greatly appreciated!

    Thanks,

    Tim



    • Edited by RayneMan Wednesday, May 23, 2018 1:44 AM HTML tables not appearing correctly - replaced with graphics
    Wednesday, May 23, 2018 1:32 AM

Answers

  • Hi RayneMan

    It looks like a "Crossjoin". From your two tables, it would give this kind of M Code:

    // Crossjoin
    let
        Warehouses = Table.FromRecords(
            {
                [Warehouse = "Warehouse A"],
                [Warehouse = "Warehouse B"],
                [Warehouse = "Warehouse C"],
                [Warehouse = "Warehouse D"]
            },
            type table [Warehouse=text]
        ),
    
        Products = Table.FromRecords(
            {  
                [Product = "Product 1"],  
                [Product = "Product 2"],
                [Product = "Product 3"]
            },
            type table [Product=text]
        ),
    
        CrossJoin = Table.AddColumn(#"Warehouses", "Products", each #"Products", type table [Product=text]),
        Expand = Table.ExpandTableColumn(#"CrossJoin", "Products", {"Product"}, {"Product"}),
        AddQuantity = Table.AddColumn(#"Expand", "Quantity", each 0, Int64.Type)
    
    in #"AddQuantity"


    Actually I am not sure to understand what you are trying to do.

    Usually you would have your two tables (called "Dimension" tables) in addition to some other tables (in your case, 1 table with all the quantities in your inventory) called "Fact" tables.
    It would looks like this:
    I added the missing Inventory table, then joined it with the two others.

    // Joins
    let
        Warehouses = Table.FromRecords(
            {
                [id=1, Warehouse = "Warehouse A"],
                [id=2, Warehouse = "Warehouse B"],
                [id=3, Warehouse = "Warehouse C"],
                [id=4, Warehouse = "Warehouse D"]
            },
            type table [id=Int64.Type, Warehouse=text]
        ),
    
        Products = Table.FromRecords(
            {  
                [id=1, Product = "Product 1"],  
                [id=2, Product = "Product 2"],
                [id=3, Product = "Product 3"]
            },
            type table [id=Int64.Type, Product=text]
        ),
    
        Inventory = Table.FromRecords(
            {  
                [Product id = 1, Warehouse id = 1, Quantity = 120],  
                [Product id = 1, Warehouse id = 3, Quantity = 50],  
                [Product id = 2, Warehouse id = 1, Quantity = 49],  
                [Product id = 2, Warehouse id = 2, Quantity = 10],  
                [Product id = 2, Warehouse id = 3, Quantity = 320],  
                [Product id = 3, Warehouse id = 2, Quantity = 770],  
                [Product id = 3, Warehouse id = 3, Quantity = 40],  
                [Product id = 3, Warehouse id = 4, Quantity = 99]
            },
            type table [Product id=Int64.Type, #"Warehouse id" = Int64.Type, #"Quantity" = Int64.Type]
        ),
    
        MergeWarehouses = Table.Join(#"Inventory", {"Warehouse id"}, #"Warehouses", {"id"}, JoinKind.LeftOuter),
        RemCol1 = Table.RemoveColumns(#"MergeWarehouses",{"Warehouse id", "id"}),
        MergeProducts = Table.Join(#"RemCol1", {"Product id"}, #"Products", {"id"}, JoinKind.LeftOuter),
        RemCol2 = Table.RemoveColumns(#"MergeProducts", {"Product id", "id"}),
        Reorder = Table.ReorderColumns(#"RemCol2", {"Warehouse", "Product", "Quantity"})
    in #"Reorder"

    In both case, all is dynamic: you can add as many Warehouses and Products, the result will be automatically adjusted.


    • Edited by anthony34 Monday, May 28, 2018 6:32 AM
    • Marked as answer by RayneMan Tuesday, May 29, 2018 3:29 PM
    Wednesday, May 23, 2018 7:04 AM

All replies

  • Wednesday, May 23, 2018 2:23 AM
  • Hi Herbert,

    Really appreciate your reply and the reference files.  I understand how your query functions when the data comes from one table, but any suggestions for my scenario where there are 2 source tables of potentially differing dimensions?

    Wednesday, May 23, 2018 3:46 AM
  • Hi RayneMan

    It looks like a "Crossjoin". From your two tables, it would give this kind of M Code:

    // Crossjoin
    let
        Warehouses = Table.FromRecords(
            {
                [Warehouse = "Warehouse A"],
                [Warehouse = "Warehouse B"],
                [Warehouse = "Warehouse C"],
                [Warehouse = "Warehouse D"]
            },
            type table [Warehouse=text]
        ),
    
        Products = Table.FromRecords(
            {  
                [Product = "Product 1"],  
                [Product = "Product 2"],
                [Product = "Product 3"]
            },
            type table [Product=text]
        ),
    
        CrossJoin = Table.AddColumn(#"Warehouses", "Products", each #"Products", type table [Product=text]),
        Expand = Table.ExpandTableColumn(#"CrossJoin", "Products", {"Product"}, {"Product"}),
        AddQuantity = Table.AddColumn(#"Expand", "Quantity", each 0, Int64.Type)
    
    in #"AddQuantity"


    Actually I am not sure to understand what you are trying to do.

    Usually you would have your two tables (called "Dimension" tables) in addition to some other tables (in your case, 1 table with all the quantities in your inventory) called "Fact" tables.
    It would looks like this:
    I added the missing Inventory table, then joined it with the two others.

    // Joins
    let
        Warehouses = Table.FromRecords(
            {
                [id=1, Warehouse = "Warehouse A"],
                [id=2, Warehouse = "Warehouse B"],
                [id=3, Warehouse = "Warehouse C"],
                [id=4, Warehouse = "Warehouse D"]
            },
            type table [id=Int64.Type, Warehouse=text]
        ),
    
        Products = Table.FromRecords(
            {  
                [id=1, Product = "Product 1"],  
                [id=2, Product = "Product 2"],
                [id=3, Product = "Product 3"]
            },
            type table [id=Int64.Type, Product=text]
        ),
    
        Inventory = Table.FromRecords(
            {  
                [Product id = 1, Warehouse id = 1, Quantity = 120],  
                [Product id = 1, Warehouse id = 3, Quantity = 50],  
                [Product id = 2, Warehouse id = 1, Quantity = 49],  
                [Product id = 2, Warehouse id = 2, Quantity = 10],  
                [Product id = 2, Warehouse id = 3, Quantity = 320],  
                [Product id = 3, Warehouse id = 2, Quantity = 770],  
                [Product id = 3, Warehouse id = 3, Quantity = 40],  
                [Product id = 3, Warehouse id = 4, Quantity = 99]
            },
            type table [Product id=Int64.Type, #"Warehouse id" = Int64.Type, #"Quantity" = Int64.Type]
        ),
    
        MergeWarehouses = Table.Join(#"Inventory", {"Warehouse id"}, #"Warehouses", {"id"}, JoinKind.LeftOuter),
        RemCol1 = Table.RemoveColumns(#"MergeWarehouses",{"Warehouse id", "id"}),
        MergeProducts = Table.Join(#"RemCol1", {"Product id"}, #"Products", {"id"}, JoinKind.LeftOuter),
        RemCol2 = Table.RemoveColumns(#"MergeProducts", {"Product id", "id"}),
        Reorder = Table.ReorderColumns(#"RemCol2", {"Warehouse", "Product", "Quantity"})
    in #"Reorder"

    In both case, all is dynamic: you can add as many Warehouses and Products, the result will be automatically adjusted.


    • Edited by anthony34 Monday, May 28, 2018 6:32 AM
    • Marked as answer by RayneMan Tuesday, May 29, 2018 3:29 PM
    Wednesday, May 23, 2018 7:04 AM
  • Tweaked PQ to accommodate two Tables of different dimensions.
    Same links.
    Thursday, May 24, 2018 1:08 AM
  • Hi Anthony,

    Thanks for taking the time to reply.  Your "Crossjoin" suggestion is exactly what I was after.  Sorry also for the confusion - my problem is a little more complex than my illustration, but this crossjoin idea was the last piece of the puzzle in finding a solution.  Perhaps it's worth my giving you an illustration closer to my situation to see if you have a better suggestion for a solution.  Basically I do have two dimension tables, as previously given:

    Warehouses = Table.FromRecords(
    	{
    		[id=1, Warehouse = "Warehouse A"],
    		[id=2, Warehouse = "Warehouse B"],
    		[id=3, Warehouse = "Warehouse C"]
    	},
    	type table [id=Int64.Type, Warehouse=text]
    ),
    
    Products = Table.FromRecords(
    	{  
    		[id=1, Product = "Product 1"],  
    		[id=2, Product = "Product 2"],
    		[id=3, Product = "Product 3"]
    	},
    	type table [id=Int64.Type, Product=text]
    ),

    And a fact table, which critically includes the warehouse, product, stock count, and date on which the count was taken:

    Stocktake = Table.FromRecords(
    	{  
    		[Warehouse id = 1, Product id = 1, Date = #date(2016,1,1), Quantity = 120],  
    		[Warehouse id = 1, Product id = 2, Date = #date(2016,1,1), Quantity = 50],  
    		[Warehouse id = 2, Product id = 2, Date = #date(2017,1,1), Quantity = 49],  
    		[Warehouse id = 2, Product id = 3, Date = #date(2017,1,1), Quantity = 10],  
    		[Warehouse id = 3, Product id = 1, Date = #date(2018,1,1), Quantity = 320],  
    		[Warehouse id = 3, Product id = 3, Date = #date(2018,1,1), Quantity = 770],  
    		[Warehouse id = 1, Product id = 3, Date = #date(2018,5,28), Quantity = 40],  
    		[Warehouse id = 3, Product id = 3, Date = #date(2018,5,28), Quantity = 99]
    	},
    	type table [#"Warehouse id" = Int64.Type, #"Product id" = Int64.Type, #"Date" = Date.Type, #"Quantity" = Int64.Type]
    ),

    I'm trying to build a query which, for each warehouse, determines the most recent stock count, discards all prior counts and assumes a zero count for products not counted on that date, then outputs the full inventory count and date at which it was valid.  So given the above fact table the result would look like:


    Here's the code I've developed so far, combining my query to determine the most recent data for each warehouse and your crossjoin with default zero values:

    let
        // Dimension tables
        Warehouses = Table.FromRecords(
            {
                [Warehouse ID=1, Warehouse = "Warehouse A"],
                [Warehouse ID=2, Warehouse = "Warehouse B"],
                [Warehouse ID=3, Warehouse = "Warehouse C"]
            },
            type table [Warehouse ID=Int64.Type, Warehouse=text]
        ),
    
        Products = Table.FromRecords(
            {  
                [Product ID=1, Product = "Product 1"],  
                [Product ID=2, Product = "Product 2"],
                [Product ID=3, Product = "Product 3"]
            },
            type table [Product ID=Int64.Type, Product=text]
        ),
    
        // Fact table
        Stocktake = Table.FromRecords(
            {  
                [Warehouse ID = 1, Product ID = 1, Date = #date(2016,1,1), Quantity = 120],  
                [Warehouse ID = 1, Product ID = 2, Date = #date(2016,1,1), Quantity = 50],  
                [Warehouse ID = 2, Product ID = 2, Date = #date(2017,1,1), Quantity = 49],  
                [Warehouse ID = 2, Product ID = 3, Date = #date(2017,1,1), Quantity = 10],  
                [Warehouse ID = 3, Product ID = 1, Date = #date(2018,1,1), Quantity = 320],  
                [Warehouse ID = 3, Product ID = 3, Date = #date(2018,1,1), Quantity = 770],  
                [Warehouse ID = 1, Product ID = 3, Date = #date(2018,5,28), Quantity = 40],  
                [Warehouse ID = 3, Product ID = 3, Date = #date(2018,5,28), Quantity = 99]
                },
            type table [#"Warehouse ID" = Int64.Type, #"Product ID" = Int64.Type, #"Date" = Date.Type, #"Quantity" = Int64.Type]
        ),
    	
        // Determine most recent stock counts from Stocktake
        #"Grouped Rows" = Table.Group(Stocktake, {"Warehouse ID"}, {{"most recent", each List.Max([Date]), type date}, {"Column", each _, type table}}),
        #"Expand Column" = Table.ExpandTableColumn(#"Grouped Rows", "Column", {"Product ID", "Date", "Quantity"}, {"Product ID", "Date", "Stocktake Quantity"}),
        #"Filtered Rows" = Table.SelectRows(#"Expand Column", each [Date] = [most recent]),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"most recent"}),
    
        // Crossjoin Warehouses and Products tables, add Default Quantity column set to zero
        #"CrossJoin" = Table.AddColumn(#"Warehouses", "Products", each #"Products", type table),
        #"Expanded Products" = Table.ExpandTableColumn(#"CrossJoin", "Products", {"Product ID", "Product"}, {"Product ID", "Product"}),
        #"Add Quantity" = Table.AddColumn(#"Expanded Products", "Default Quantity", each 0, Int64.Type),
    
        // Merge results, tidy up, and sort
        #"Merge Tables" = Table.NestedJoin(#"Add Quantity",{"Warehouse ID", "Product ID"}, #"Removed Columns", {"Warehouse ID", "Product ID"},"Stocktake Qty",JoinKind.LeftOuter),
        #"Expanded Stocktake Qty" = Table.ExpandTableColumn(#"Merge Tables", "Stocktake Qty", {"Date", "Stocktake Quantity"}, {"Date", "Stocktake Quantity"}),
        #"Added Conditional Column" = Table.AddColumn(#"Expanded Stocktake Qty", "Quantity", each if [Stocktake Quantity] <> null then [Stocktake Quantity] else [Default Quantity]),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Warehouse ID", "Product ID", "Date", "Quantity"}),
        #"Grouped Rows2" = Table.Group(#"Removed Other Columns", {"Warehouse ID"}, {{"Date", each List.Max([Date]), type date}, {"Column", each _, type table}}),
        #"Expand Column2" = Table.ExpandTableColumn(#"Grouped Rows2", "Column", {"Product ID", "Quantity"}, {"Product ID", "Quantity"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Expand Column2",{"Warehouse ID", "Product ID", "Date", "Quantity"}),
        #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Warehouse ID", Order.Ascending}, {"Product ID", Order.Ascending}})
    in
        #"Sorted Rows"

    The output is my desired result, but I feel like there should be a more eloquent solution and wondered if you have any suggestions?



    • Edited by RayneMan Monday, May 28, 2018 7:31 AM Updated with my latest solution code
    Monday, May 28, 2018 5:53 AM