Combine Rows but concatenate on a certain field in Excel Power Query or Microsoft SQL

user_10138634 0
Viewed 148

I have brought a table from an Authority database into Excel via power query OBDC type, that includes fields like:

  1. Date - various
  2. Comments - mem_txt
  3. Sequence - seq_num

The Comments field has a length restriction, and if a longer string is entered, it returns multiple rows with the Comments field being chopped into suitable lengths and the order returned in the Sequence field as per extract below. All other parts of the records are the same. enter image description here

I want to collapse the rows based and concatenate the various Comments into a single entry. There is a date/time column just outside of the screen shot above that can be used to group the rows by (it is the same for the set of rows, but unique across the data set).

For example: enter image description here

I did try bring the data in by a query connection, using the GROUP_CONCAT(Comments SEPARATOR ', ') and GROUP BY date, but that command isn't available in Microsoft Query.

powerquery excel sql
1 Answers

Assuming the date/time column you refer to is named date_time, the M code would be:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Grouped Rows" = Table.Group(
    Source, 
    {"date_time"}, 
    {{"NewCol", each Text.Combine([mem_text])}}
  )
in
  #"Grouped Rows"

Amend the Source line as required.

user_170077040
Related