How do I connect to another table with multiple delimited values in the same column?

[t_guet01]

Hey guys,

I am facing an issue that I would like to connect customer data to a transaction table. Within the transaction table, everything is given as expected. However, the customer data are not unambiguous. More specifically, I find multiple CustomerIDs in the same cell per customer. The customer IDs are delimited mostly by "/" (sometimes even ";"). During the years, some customers got new IDs. Within the transaction table, any Customer ID (however only one per cell) could be given.

Example:

Customer data:

Customer ID	Customer Name
A123	A-Corp
B456 / B789	B-Company
C123 / C456 / C789	C-Limited
D987 ; D654	D-Group

Transaction data:

OrderID	CustomerID	InvoiceAmount
100078	123	100
100079	B456	200
100080	B789	50
100081	D654	300

In this example, customer "B-Company" has two possible customer IDs (given in the customer table) which both appear in the transaction table. I would like to match both transaction lines to "B-Company" to show an aggregated sum - in this example 250 (200 + 50).

How could I link the customer data to the transaction data, so that the tables all delimited CustomerIDs are respected?

Thanks in advance and best

T

[DataInsights · Accepted solution]

@t_guet01,

Try this in Power Query for the Customer table. Overview:

1. Use "Replace value" so that one delimiter is used throughout the table.

2. Use "Split column by delimiter" and split into rows.

3. Use "Trim" to remove blank spaces in Customer ID.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WcjQ0MlbSUXLUdc4vKlCK1YlWcjIxNVPQV3Ayt7AESjgBJXILEvMqwXLOQNVAOWeIEmeIEmddn8zczJLUFLASF0sLcwVrBRczUxOgnIuue1F+KdDgWAA=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"Customer ID" = _t, #"Customer Name" = _t]
  ),
  ChangeType = Table.TransformColumnTypes(
    Source,
    {{"Customer ID", type text}, {"Customer Name", type text}}
  ),
  ReplaceValue = Table.ReplaceValue(ChangeType, ";", "/", Replacer.ReplaceText, {"Customer ID"}),
  SplitColumn = Table.ExpandListColumn(
    Table.TransformColumns(
      ReplaceValue,
      {
        {
          "Customer ID",
          Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv),
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ),
    "Customer ID"
  ),
  ChangeType2 = Table.TransformColumnTypes(SplitColumn, {{"Customer ID", type text}}),
  TrimText = Table.TransformColumns(ChangeType2, {{"Customer ID", Text.Trim, type text}})
in
  TrimText