Forum Discussion

t_guet01's avatar
t_guet01
Icon for Helper I rankHelper I
4 years ago
Solved

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

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 IDCustomer Name
A123A-Corp
B456 / B789B-Company
C123 / C456 / C789C-Limited
D987 ; D654D-Group

 

Transaction data:

OrderIDCustomerIDInvoiceAmount
100078123100
100079B456200
100080B78950
100081D654300

 

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

Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    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

     

     

1 Reply

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    4 years ago

    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