Forum Discussion

mejiaks's avatar
mejiaks
Frequent Visitor
8 years ago

merge two tables based on codition

Good evening guys

 

I have a situation I need help with.

 

I have a table with a field name "Docket ID"

 

and other table with column named "Recievings

 

Table A looks like this

 

Docket ID    Reciving Date

W01            01/01/2018

W02            02/01/2018

W03            03/01/2018

W04            03/10/2018

W04            03/10/2018

 

and table B looks like this

 

Shipment No      Shipment Arrival   Recievings

S01                      01/10/2018            W01

S02                      03/10/2018            W02, W03, W04

S03                      03/15/2018            W05

 

The result I am looking for wiould be something like this

 

Table C

Docket ID    Reciving Date    Shipment Arriva;l

W01            01/01/2018         01/10/2018

W02            02/01/2018         03/10/2018

W03            03/01/2018         03/10/2018

W04            03/10/2018         03/10/2018

W05            03/10/2018         03/15/2018

 

is is posible to achive this?

 

 

 

General Comment

1 Reply

  • Seward12533's avatar
    Seward12533
    Solution Sage
    8 years ago

    The best way to tackle this is convert you Table B into a more of a database format like this. 

    Shipment No Shipment Arrival Docket ID

    S011/10/2018W01
    S023/10/2018W02
    S023/10/2018W03
    S023/10/2018W04
    S033/15/2018W05

     

    You can do this in Power Query (Edit Queries)

    1. Edit your Query for Table B
    2. Select the Receiving Column and choose Split Column from the Transform Ribbon
      1. Be sure to set the Advanced Option for number of colums to something bigger than the most number of comma separated values you expect to have in your data.  I used 20 in my test.
    3. Select all teh Columns Receivign.1 ... Receivings.n and then chose "Unpivot Columns" form the Transform Ribbon
    4. Remove the Attributes Column
    5. Raname the values Column

    now you can creat a bridge table for all your Docket IDs (in Power Query:

    1. make a copy of Table A (you can use Reference instead of duplicate) 
    2. Select the Docket ID column
    3. Remove other columns
    4. Remove Duplicates
    5. Rename the Query to Docket IDs

    In your data model you can now relate both Table A and Table B to Document ID's and build your Table C as a visual.

    You could also generate it in Power Query using Table Merge (new Query->Combine->Merge if you want.

     

     

    Here is the M language for the Query I described above.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjYwVNJRMtQ3NNA3tACywoH8WB2QuBGQZ4wkbqQTbmAMxCZQeWOIvClM3lQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Shipment No" = _t, #"Shipment Arrival" = _t, Receivings = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Shipment No", type text}, {"Shipment Arrival", type date}, {"Receivings", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Receivings", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Receivings.1", "Receivings.2", "Receivings.3", "Receivings.4", "Receivings.5", "Receivings.6", "Receivings.7", "Receivings.8", "Receivings.9", "Receivings.10", "Receivings.11", "Receivings.12", "Receivings.13", "Receivings.14", "Receivings.15", "Receivings.16", "Receivings.17", "Receivings.18", "Receivings.19", "Receivings.20"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Shipment No", "Shipment Arrival"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Docket ID"}})
in
    #"Renamed Columns"