取消資料列樞紐
在 Power Query 中,您可以將資料行轉換成屬性值組,其中數據行會變成數據列。
顯示左數據表的空白資料行和數據列,以及屬性值 A1、A2 和 A3 做為數據行標頭的圖表。 在此表格中,A1 資料行包含 V1、V4 和 V7 的值。 A2 資料行包含 V2、V5 和 V8 值。 A3 資料列包含 V3、V6 和 V9 的值。 取消樞紐數據行時,圖表的右數據表包含空白數據行和數據列、具有 A1、A2 和 A3 九個數據列的 Attributes 數據行重複三次,以及值為 V1 到 V9 的 Values 數據行。
例如,假設有如下的數據表,其中國家/地區數據列和日期數據行會建立值的矩陣,因此很難以可調整的方式分析數據。
數據表的螢幕快照,其中包含 Text 數據類型中設定的 Country 數據行,以及日期為 2023 年 6 月 1 日、2023 年 7 月 1 日和 2023 年 8 月 1 日設定為整數數據類型的三個數據行。 Country 數據行包含美國數據列 1、加拿大第 2 列,第 3 列包含巴拿馬。
相反地,您可以將數據表轉換成具有未樞紐數據行的數據表,如下圖所示。 在已轉換的數據表中,比較容易使用日期做為屬性來篩選。
數據表的螢幕快照,其中含有設定為 Text 資料類型的國家 /地區數據行、將 [屬性] 資料行設定為 [文字] 資料類型,以及設定為 [整數] 數據類型的 [值] 資料行。 Country 數據行包含美國的前三個數據列、接下來三個數據列中的加拿大,最後三個數據列包含巴拿馬。 [屬性] 數據行包含 2023 年 6 月 1 日,第一個、第四列和第七個數據列的 2023 年 7 月 1 日、第二個、第五列和第八個數據列的 2023 年 8 月 1 日,以及第三、第六和第九個數據列的 2023 年 8 月 1 日。
此轉換中的索引鍵是您在數據表中擁有一組日期,而該日期應該全部是單一數據行的一部分。 每個日期和國家/地區的個別值都應該在不同的數據行中,有效地建立屬性值組。
Power Query 一律會使用兩個數據行來建立屬性值組:
- 屬性:已取消樞紐的數據行標題名稱。
- 值:每個未樞紐數據行標題底下的值。
使用者介面中有多個位置,您可以在其中找到 [取消樞紐] 數據行。 您可以在您想要取消樞紐的數據行上按下滑鼠右鍵,也可以從 功能區中的 [轉換 ] 索引標籤選取命令。
有三種方式可從數據表取消樞紐數據行:
- 取消資料列樞紐
- 取消樞紐其他數據行
- 僅取消樞紐選取的數據行
針對先前所述的案例,您必須先選取您想要取消樞紐的數據行。 您可以視需要選取多個資料行時選取 Ctrl 。 在此案例中,您想要選取除一個名為 Country 以外的所有數據行。 選取數據行之後,以滑鼠右鍵按下任何選取的數據行,然後選取 [取消樞紐數據行]。
數據表的螢幕快照,其中已選取 2023 年 6 月 1 日、2023 年 7 月 1 日和 2023 年 8 月 1 日的數據行,以及快捷方式功能表中選取的 [取消樞紐數據行] 命令。
該作業的結果會產生下圖所示的結果。
從先前步驟建立查詢之後,假設您的初始數據表會更新為類似下列螢幕快照。
具有相同原始國家/地區、2023 年 6 月 1 日、2023 年 7 月 1 日和 2023 年 8 月 2023 年 8 月 1 日日期數據行之數據表的螢幕快照,其中新增了 2023 年 9 月 1 日日期數據行。 Country 數據行仍然包含美國、加拿大和巴拿馬的值,但也已將英國新增至第四列,墨西哥則新增至第五列。
請注意,您正在新增 2023 年 9 月 1 日日期的新數據行(2023 年 9 月 1 日),以及英國和墨西哥國家/地區兩個新數據列。
如果您重新整理查詢,請注意作業是在更新的數據行上完成,但不會影響原本未選取的數據行(在此範例中為國家/地區)。 這表示您加入源數據表的任何新數據行也會取消樞紐。
下圖顯示使用新更新的源數據表重新整理之後,查詢的外觀。
[國家/地區]、[屬性] 和 [值] 資料行的數據表螢幕快照。 Country 數據行的前四個數據列包含 USA、第二個四個數據列包含加拿大、第三個四個數據列包含巴拿馬、第四個四個數據列包含英國,而第五個四個數據列包含墨西哥。 [屬性] 數據行包含 2023 年 6 月 1 日、2023 年 7 月 1 日和 2023 年 8 月 2023 年 8 月前四個數據列的日期,每個國家/地區都會重複這些日期。
您也可以選取您不想取消樞紐的數據行,並取消樞紐數據表中其餘的數據行。 此作業是將其他數據行取消樞紐作用的地方。
該作業的結果會產生與您從 Unpivot 資料行取得的結果完全相同。
數據表的螢幕快照,其中含有設定為 Text 資料類型的國家 /地區數據行、將 [屬性] 資料行設定為 [文字] 資料類型,以及設定為 [整數] 數據類型的 [值] 資料行。 Country 數據行包含美國的前三個數據列、接下來三個數據列中的加拿大,最後三個數據列包含巴拿馬。 [屬性] 數據行包含 2023 年 6 月 1 日,第一個、第四列和第七個數據列的 2023 年 7 月 1 日、第二個、第五列和第八個數據列的 2023 年 8 月 1 日,以及第三、第六和第九個數據列的 2023 年 8 月 1 日。
注意
此轉換對於具有未知數據行數目的查詢而言非常重要。 此作業會將數據表中的所有數據行取消樞紐,但您所選取的數據行除外。 如果案例的數據源在重新整理中取得新的日期數據行,這是理想的解決方案,因為這些數據行將會被挑選並取消擷取。
類似於 取消樞紐數據行 作業,如果您的查詢重新整理,並從數據源中挑選更多數據,則除了先前選取的數據行以外,所有數據行都會取消樞紐處理。
為了說明此程式,假設您有類似下圖中的新數據表。
2023 年 6 月 1 日、2023 年 7 月 1 日、2023 年 8 月 1 日和 2023 年 9 月 1 日數據行的數據表螢幕快照,其中所有數據行都設定為 Text 數據類型。 Country 數據行包含從上到下、美國、加拿大、巴拿馬、英國和墨西哥。
您可以選取 [ 國家/地區 ] 數據行,然後選取 [取消樞紐其他數據行],這會產生下列結果。
[國家/地區]、[屬性] 和 [值] 資料行的數據表螢幕快照。 Country 和 Attribute 資料行會設定為 Text 數據類型。 Value 資料行會設定為 [整數] 值數據類型。 Country 數據行的前四個數據列包含 USA、第二個四個數據列包含加拿大、第三個四個數據列包含巴拿馬、第四個四個數據列包含英國,而第五個四個數據列包含墨西哥。 [屬性] 數據行包含 2023 年 6 月 1 日、2023 年 7 月 1 日、2023 年 8 月 1 日和 2023 年 9 月 1 日,這四個數據列會針對每個國家/地區重複。
最後一個選項的目的是只取消數據表中的特定數據行。 此選項對於您處理數據源中未知數目的數據行,且只想要取消選取的數據行,這一選項很重要。
若要執行這項作業,請選取要取消樞紐的數據行,在此範例中為 Country 數據行以外的所有數據行。 然後以滑鼠右鍵按下您選取的任何數據行,然後選取 [僅取消樞紐選取的數據行]。
請注意此作業如何產生與先前範例相同的輸出。
數據表的螢幕快照,其中含有設定為 Text 資料類型的國家 /地區數據行、將 [屬性] 資料行設定為 [文字] 資料類型,以及設定為 [整數] 數據類型的 [值] 資料行。 Country 數據行包含美國的前三個數據列、接下來三個數據列中的加拿大,最後三個數據列包含巴拿馬。 [屬性] 數據行包含 2023 年 6 月 1 日,第一個、四個和第七個數據列的 2023 年 7 月 1 日、第二個、第五個和第八個數據列,以及 2023 年 8 月 1 日在第三、第六和第九個數據列中的 2023 年 8 月 1 日。
重新整理之後,如果我們的源數據表變更為有新的 9/1/2020 數據行,以及英國和墨西哥的新數據列,則查詢的輸出與先前的範例不同。 假設在重新整理之後,我們的源數據表會變更為下圖中的數據表。
查詢的輸出看起來像下圖。
這看起來像這樣,因為未樞紐作業只會套用在 2020 年 6 月 1 日、 7/1/2020 和 8/1/2020 數據行上,因此標頭 為 9/1/2020 的數據行會保持不變。