根據列的值合併多行中的多個列，直到值發生變化

Question

參考附帶的帶有範例資料的圖像，我想將多行中的日期、人員和註釋合併為 1 行，並為每組組合值提供換行符。每個註解佔據一行，名稱和 ID 為空，因此主行下包含值的每一行都需要合併。請參閱附圖以了解資料和所需的輸出。

樣本資料：

+-------+--------------+------------------+---------+-----------------------+
| Name  |      ID      |       Date       | Person  |         Notes         |
+-------+--------------+------------------+---------+-----------------------+
| Danny | UID11224512  | 11/5/2019        | Joe     | <p>Note for Danny</p> |
|       |              | 11/5/2019        | John    | <p>Note 2</p>         |
| Joe   | UID11224956  | 11/5/2019 18:54  | Someone | <p>Note 1</p>         |
|       |              | 11/5/2019 18:54  | Someone | <p>Note 2</p>         |
| Jane  | UID11224959  | 11/5/2019 18:54  | Danny   | <p>Note 1 </p>        |
|       |              | 11/5/2019 17:33  | Shawn   | <p>Note 3</p>         |
|       |              | 11/6/2019 18:54  | Jane    | <p>Note 2</p>         |
| Tom   | UID115466652 |                  |         |                       |
| Eric  | UID168998955 |                  |         |                       |
| Paula | UID166559885 |                  |         |                       |
| Frank | UID112249569 | 11/5/2019 18:54  | Someone | <p>Note 1</p>         |
|       |              | 11/6/2019 18:54  | Someone | <p>Note 2</p>         |
|       |              | 11/7/2019 18:54  | Someone | <p>Note 3</p>         |
|       |              | 11/8/2019 18:54  | Someone | <p>Note 4</p>         |
|       |              | 11/9/2019 18:54  | Someone | <p>Note 5</p>         |
|       |              | 11/10/2019 18:54 | Someone | <p>Note 6</p>         |
| Paul  | UID1665588   |                  |         |                       |
+-------+--------------+------------------+---------+-----------------------+

期望的輸出：

+-------+--------------+-----------------------------------+
| Name  |      ID      |               Notes               |
+-------+--------------+-----------------------------------+
| Danny | UID11224512  | "11/5/2019 (Joe) - Note for Danny |
|       |              | 11/5/2019 (John) - Note 2"        |
| Joe   | UID11224956  | "11/5/2019 (Someone) - Note 1     |
|       |              | 11/5/2019 (Someone) - Note 2"     |
| Jane  | UID11224959  | "11/5/2019 (Danny) - Note 1       |
|       |              | 11/5/2019 (Shawn) - Note 2        |
|       |              | 11/5/2019 (Jane) - Note 3"        |
| Tom   | UID115466652 |                                   |
| Eric  | UID168998955 |                                   |
| Paula | UID166559885 |                                   |
| Frank | UID112249569 | "11/5/2019 (Someone) - Note 1     |
|       |              | 11/6/2019 (Someone) - Note 2      |
|       |              | 11/7/2019 (Someone) - Note 3      |
|       |              | 11/8/2019 (Someone) - Note 4      |
|       |              | 11/9/2019 (Someone) - Note 5      |
|       |              | 11/10/2019 (Someone) - Note 6"    |
| Paul  | UID1665588   |                                   |
+-------+--------------+-----------------+---------+-----------------------------------+


    

樣本資料：

想要的輸出：

Answer 1

執行此操作的一種方法是使用 Windows Excel 2010+ 和 Office 365 中提供的 Power Query。

• 選擇資料表中的某個儲存格
  • Data => Get&Transform => from Table/Range
  • 在 Power Query 使用者介面中：Home => Advanced Editor
  • 記下第 2 行中的表格名稱
  • 將下面的 MCode 貼到視窗中
  • 將第 2 行中的表格名稱變更為您的實際表格名稱
  • 閱讀程式碼、註釋並檢查Applied Steps視窗以了解發生了什麼。

簡要演算法

• Fill down名稱和 ID 列的空白
• Group按名稱和 ID 列
• 提取第一的日期、人物和註釋顯示在單獨的欄位中
• 合併最後一列的所有日期/人員/註釋（以換行符號分隔）

M程式碼
在下面的程式碼中，請務必將第2行中的表名稱變更為您實際的表名稱

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"ID", type text}, {"Date", type datetime}, {"Person", type text}, {"Notes", type text}}),
    
//Fill down to fill in the blanks for Name and ID columns
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Name", "ID"}),

//Group on Name and ID -- should be the same so this is a double check.
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Name", "ID"}, {

//For each Name/ID
    //Extract first Date as a date string if it has no time component, otherwise leave it as a datetime 
        {"Date", each if DateTime.Time([Date]{0}) = #time(0,0,0)
                            then DateTime.ToText([Date]{0},"MM/dd/yy")
                            else [Date]{0}},
    //Extract first Person
        {"Person", each [Person]{0}},

    //extract first Note
        {"Notes.1", each [Notes]{0}},

    //Extract and combine, for each note, the Date, person, and note
    //  Then split the combination for each entry by linefeed.
        {"Notes.2", (t)=> Text.Combine(
                            List.Generate(() => [Count=1, 
                            dt =DateTime.ToText(t[Date]{0},"MM/dd/yyyy") & " (" & 
                                t[Person]{0} & 
                                ") - " & t[Notes]{0}],
                         each [Count] <= Table.RowCount(t), 
                         each [Count = [Count] + 1, dt = DateTime.ToText(t[Date]{[Count]},"MM/dd/yyyy") & 
                                    " (" & t[Person]{[Count]} & 
                                    ") - " & t[Notes]{[Count]}],
                         each [dt]),"#(lf)")
                    }
        })
in
    #"Grouped Rows"

數據

結果

在 Excel 中，您需要將列Notes.2格式設定Wrap Text為 (Datemm/dd/yyyy hh:mm在日期列中，沒有時間的日期作為文字字串返回，因此不會具有相同的格式 - 正如您在問題中所示的那樣）

Answer 2

請務必在主頁功能區中為整個評論欄設定「文字換行」。使用以下公式將文字值與換行符號組合起來：

="comment1" & CHAR(10) & "Comment2"

如果您要合併兩個單獨的表，那麼可能值得研究 Excel 中的 Power Query。