Forum Discussion
Concatanate values in the same column when defined criteria match from value in another column
I'm using Power Query in Power BI.
Here is an example of the data I'm looking at. The data is currently grouped so the # of rows with each "Test Interpretation" is displayed in the end column as labeled. There is a "Test Date" field and in the group by function, I've removed that field and am showing the "Test Date" Min and "Test Date" Max.
Here is what that operation looks like:
I want to display the information so each row with a unique "Accession_Num" has only 1 row of data. I'd like the "Sample Type, Patient or Pooled" and "Test Interpretation" fields to concatanate into a new field and the "Test Date Min" to represent the easliest "Test Date" for the "Accession_Num" and the "Test Date Max" to represent the latest "Test Date" for the "Accession_Num".
PIN | Accession_Num | Sample Type, Patient or Pooled | Test Interpretation | Test Date Min | Test Date Max | # rows with test interpretation |
ABC | 1245 | Milk (ind or pooled), Patient | NDET | 6/23/2024 | 6/25/2024 | 300 |
ABC | 1245 | Milk (ind or pooled), Patient | DET | 6/24/2024 | 6/24/2024 | 30 |
ABC | 1245 | Bulk Tank Milk, Pooled | NDET | 6/25/2024 | 6/26/2024 | 60 |
ABC | 456 | Milk (ind or pooled), Patient | NDET | 6/25/2024 | 6/25/2024 | 300 |
ABC | 654 | Milk (ind or pooled), Patient | NDET | 6/25/2024 | 6/25/2024 | 300 |
DEF | 765 | Milk (ind or pooled), Patient | NDET | 6/23/2024 | 6/24/2024 | 60 |
DEF | 765 | Bulk Tank Milk, Patient | NDET | 6/24/2024 | 6/24/2024 | 30 |
ABC | 987 | Milk (ind or pooled), Patient | NDET | 6/26/2024 | 6/26/2024 | 90 |
HIJ | 789 | Bulk Tank Milk, Patient | NDET | 6/21/2024 | 6/21/2024 | 30 |
I would like the final results to look like this:
PIN | Accession_Num | Sample Type, Patient or Pooled_New | Test Interpretation_New | Test Date Min | Test Date Max | # rows with test interpretation |
ABC | 1245 | Milk (ind or pooled), Patient; Milk (ind or pooled), Patient; Bulk Tank Milk, Pooled | NDET; DET; NDET | 6/23/2024 | 6/26/2024 | 300; 30; 60 |
ABC | 456 | Milk (ind or pooled), Patient | NDET | 6/25/2024 | 6/25/2024 | 300 |
ABC | 654 | Milk (ind or pooled), Patient | NDET | 6/25/2024 | 6/25/2024 | 300 |
DEF | 765 | Milk (ind or pooled), Patient; Bulk Tank Milk, Patient | NDET; NDET | 6/23/2024 | 6/24/2024 | 60; 30 |
ABC | 987 | Milk (ind or pooled), Patient | NDET | 6/26/2024 | 6/26/2024 | 90 |
HIJ | 789 | Bulk Tank Milk, Patient | NDET | 6/21/2024 | 6/21/2024 | 30 |
I think I could remove the "Test Date" and try to concatantate the "Sample Type, Patient or Pooled" and "Test Interpretation" fields and then bring the "Test Date" Min and Max back in via the group by again, maybe?
Any ideas on how to accomplish getting the output in Power Query to resemble that second table? Thanks all!
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdDRaoMwFAbgVzm4mw6Eqk1ivWxnxxxUhHknMqQNNNQmoind4Dz86mpkOuhadnc05Mt//iyzkijGxWbDm0Yo+R4fD/hWHKqSQ/pZcRuSQgsuNagaEqVKvsWUNxoiqXld1Vyfj5W8/AsLzWEtBl/FBz5ArU4NnITegW5PxPCulduZtVg+oesRimtR7mEi5LZ9sfp+8bFPgXG4SpFNvdnUczzSTvQyzRznLqhzSO8Q44yY5fHMpIXcQwvapgQThPYA66YfAKHsxn3o1X0YJf92wtUz+uz+fslwLcP8qmUEXC02mPs35mDjeoOOeYle0Z8Hf+Zwe8A1Oaw8/wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8"}), #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]), #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{""}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"PIN", type text}, {"Accession_Num", Int64.Type}, {"Sample Type, Patient or Pooled", type text}, {"Test Interpretation", type text}, {"Test Date Min", type date}, {"Test Date Max", type date}, {"# rows with test interpretation", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"PIN", "Accession_Num"}, {{"Sample Type, Patient or Pooled", each Text.Combine([#"Sample Type, Patient or Pooled"],"; "),type nullable text},{"Test Interpretation", each Text.Combine([Test Interpretation],"; "),type nullable text},{"Test Date Min", each List.Min([Test Date Min]), type nullable date}, {"Test Date Max", each List.Max([Test Date Max]), type nullable date}, {"# rows with test interpretation", each Text.Combine([#"# rows with test interpretation"],"; "), type nullable text}}) in #"Grouped Rows"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 Source step with your own source.
1 Reply
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdDRaoMwFAbgVzm4mw6Eqk1ivWxnxxxUhHknMqQNNNQmoind4Dz86mpkOuhadnc05Mt//iyzkijGxWbDm0Yo+R4fD/hWHKqSQ/pZcRuSQgsuNagaEqVKvsWUNxoiqXld1Vyfj5W8/AsLzWEtBl/FBz5ArU4NnITegW5PxPCulduZtVg+oesRimtR7mEi5LZ9sfp+8bFPgXG4SpFNvdnUczzSTvQyzRznLqhzSO8Q44yY5fHMpIXcQwvapgQThPYA66YfAKHsxn3o1X0YJf92wtUz+uz+fslwLcP8qmUEXC02mPs35mDjeoOOeYle0Z8Hf+Zwe8A1Oaw8/wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8"}), #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]), #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{""}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"PIN", type text}, {"Accession_Num", Int64.Type}, {"Sample Type, Patient or Pooled", type text}, {"Test Interpretation", type text}, {"Test Date Min", type date}, {"Test Date Max", type date}, {"# rows with test interpretation", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"PIN", "Accession_Num"}, {{"Sample Type, Patient or Pooled", each Text.Combine([#"Sample Type, Patient or Pooled"],"; "),type nullable text},{"Test Interpretation", each Text.Combine([Test Interpretation],"; "),type nullable text},{"Test Date Min", each List.Min([Test Date Min]), type nullable date}, {"Test Date Max", each List.Max([Test Date Max]), type nullable date}, {"# rows with test interpretation", each Text.Combine([#"# rows with test interpretation"],"; "), type nullable text}}) in #"Grouped Rows"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 Source step with your own source.