merge two tables based on codition

[mejiaks]

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?

[Seward12533]

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

S01	1/10/2018	W01
S02	3/10/2018	W02
S02	3/10/2018	W03
S02	3/10/2018	W04
S03	3/15/2018	W05

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"