Table with 2 Arrays, I need to associated entries frome each column

[BudGarrison]

Let me preface this with saying: I am working with an animal welfare organization that uses a commercial software package that provides their semantic models but not direct access to their database and I am admittedly relatively new to PowerBI, but have worked with some basic SQL and other reporting tools in the past.

My challenge is that I need to find the date that an animal was made available for adoption. The status is in 1 column that may be combined with other status' in a comma separated array. The date the status was assigned is in another field in a similar array. The date is in the same order as the status field so if Availabe for adoption is in the 2nd position in the 1st array, the date it was assigned is also in the 2nd position in the 2nd array. Ideally I need to end up with a column titled Available for Adoption that contains the date the status was assigned. I haven't found a way to do this in DAX.

Any ideas to send me down the right path? I can promise many cute animal pics as a reward 🙂

Sample data:

Animal	Current Sub-Statuses	Current Sub-Status Dates
314248	Available for Adoption	12-Oct-2024 5:38 PM
316772	Available for Adoption	26-Aug-2024 11:29 AM
318509	Available for Adoption	20-Aug-2024 1:24 PM
319783	Available for Adoption	20-Aug-2024 1:25 PM
319815	Available for Adoption	20-Aug-2024 1:25 PM
320803	Available for Adoption	13-Oct-2024 6:44 PM
320804	Available for Adoption	13-Oct-2024 6:43 PM
323205	Available for Adoption, Awaiting Spay/Neuter	01-Oct-2024 9:32 AM, 28-Aug-2024 7:21 AM
324514	Available for Adoption, Awaiting Spay/Neuter	16-Oct-2024 9:47 PM, 19-Sep-2024 12:40 PM
307931	Available for Adoption, Under Behavior Care	20-Aug-2024 1:27 PM, 19-Jun-2024 4:40 PM
323178	Foster Requested	05-Oct-2024 3:03 PM
315606	Foster Requested, Medical Consult	06-Sep-2024 10:59 AM, 27-Aug-2024 8:26 PM
320175	Foster Requested, Medical Consult	25-Sep-2024 11:39 AM, 13-Sep-2024 5:25 PM
314189	Medical Consult, Available for Adoption	19-Sep-2024 12:15 PM, 20-Aug-2024 1:34 PM
316130	Medical Consult, Available for Adoption	19-Sep-2024 9:04 AM, 20-Aug-2024 1:20 PM
324948	Medical Consult, Available for Adoption, Transfer Requested, Awaiting Spay/Neuter	06-Oct-2024 10:29 AM, 22-Sep-2024 1:13 PM, 22-Sep-2024 1:10 PM, 22-Sep-2024 1:10 PM
323177	Medical Consult, Foster Requested	29-Sep-2024 3:21 PM, 25-Sep-2024 11:37 AM
282957	Transfer Requested, Medical Consult	08-Sep-2024 12:08 PM, 07-Sep-2024 9:29 AM

[BudGarrison]

Thanks! I will try this out. Charlotte is your reward 🙂

[lbendlin]

This one is a bit more streamlined

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVTLboMwEPwVK2eietdv32ikHir1oT5OUQ80cVOkCFICrfr3NSEBk0LU9ILQmpndnRkzn08YcOR6Ek3izyRdJ69rR97ygsTLfFOmeeYPAKd3i3KKFDkRlmlyfzN5iWqkVApPIVFO42rVIAEsGhIfoFpQcxJKA6j1j7apUZqdgRQBUoP4FxKppid7AuskkpbzHpKfgWQd0mPHp41I/JWkZZqtyOMm+b64dVXpCv85hY7PWIZe8Yig7hZTFqG1AbmA8fFGm4AMm3Dlh44ImOmj2+zVQ8tpuwpVhsGJLs/Z0hXk0r0nn6k/mCWF++1G2+S6ypoiD3sgA1Wn+Crf+hnJg/uonH9b1pKIblpmaScxCEnlACYiN26ZLpI1meXZtlqXNYkMtqNWmEZY1c2oLcrAd1Dij9QoAmqwrKH2wWirohdjDrq+OkdE3qzRjPWdAbGTsq8vC26YBEb/3cBYyhtt+v4FVnGz++H8kT8iT0WSbd/6Mo7FP0imtwn3NmEggAXW7N8v0rFily81NPRA4DCQg9XXbcd85LI6XELUaERNPbTmQBB1z0yqd+xUhQ7sf7QvPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Animal = _t, #"Current Sub-Statuses" = _t, #"Current Sub-Status Dates" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Status", each Table.FromList(List.Transform(List.Zip({Text.Split([#"Current Sub-Statuses"],", "),Text.Split([#"Current Sub-Status Dates"],", ")}),each _{0} & "," & _{1}))),
    #"Expanded Status1" = Table.ExpandTableColumn(#"Added Custom", "Status", {"Column1", "Column2"}, {"Sub-Status", "Sub-Status Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Status1",{{"Sub-Status Date", type datetime}, {"Sub-Status", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Animal", "Sub-Status", "Sub-Status Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([#"Sub-Status"] = "Available for Adoption"))
in
    #"Filtered Rows"

Here is the part where stuff happens

#"Added Custom" = Table.AddColumn(
    Source, 
    "Status", 
    each Table.FromList(
      List.Transform(
        List.Zip(
          {
            Text.Split([#"Current Sub-Statuses"], ", "), 
            Text.Split([#"Current Sub-Status Dates"], ", ")
          }
        ), 
        each _{0} & "," & _{1}
      )
    )
  )

The rest is just type changes etc.

[lbendlin · Accepted solution]

I am sure there are more elegant ways of doing this but here is one option:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVTLboMwEPwVK2eietdv32ikHir1oT5OUQ80cVOkCFICrfr3NSEBk0LU9ILQmpndnRkzn08YcOR6Ek3izyRdJ69rR97ygsTLfFOmeeYPAKd3i3KKFDkRlmlyfzN5iWqkVApPIVFO42rVIAEsGhIfoFpQcxJKA6j1j7apUZqdgRQBUoP4FxKppid7AuskkpbzHpKfgWQd0mPHp41I/JWkZZqtyOMm+b64dVXpCv85hY7PWIZe8Yig7hZTFqG1AbmA8fFGm4AMm3Dlh44ImOmj2+zVQ8tpuwpVhsGJLs/Z0hXk0r0nn6k/mCWF++1G2+S6ypoiD3sgA1Wn+Crf+hnJg/uonH9b1pKIblpmaScxCEnlACYiN26ZLpI1meXZtlqXNYkMtqNWmEZY1c2oLcrAd1Dij9QoAmqwrKH2wWirohdjDrq+OkdE3qzRjPWdAbGTsq8vC26YBEb/3cBYyhtt+v4FVnGz++H8kT8iT0WSbd/6Mo7FP0imtwn3NmEggAXW7N8v0rFily81NPRA4DCQg9XXbcd85LI6XELUaERNPbTmQBB1z0yqd+xUhQ7sf7QvPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Animal = _t, #"Current Sub-Statuses" = _t, #"Current Sub-Status Dates" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Status", each List.Zip({Text.Split([#"Current Sub-Statuses"],", "),Text.Split([#"Current Sub-Status Dates"],", ")})),
    #"Expanded Status" = Table.ExpandListColumn(#"Added Custom", "Status"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Status", {"Status", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Status", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"Current Sub-Status", "Current Sub-Status Date"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"Animal", "Current Sub-Status", "Current Sub-Status Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Current Sub-Status Date", type datetime}})
in
    #"Changed Type"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

A dog picture, please.