⑴ power query 處理100萬行數據好用嗎
100萬對於PQ來說沒問題的。更多Power BI信息和課程歡迎關注「PowerPivot工坊」的「從Excel到Power BI數據分析可視化」系列課程。
⑵ 如何在Power Query中提取數據——列表篇(1)
表名為列表，第一步驟名稱為源 (一)從頭開始提取 1. 獲取列表第一個 List.First (list as list , optional defaultValue as any )as any 返回列表的第1個值，參數2代表如果列表為空，可以指定返回一個值。 例： List.First({1..10})=1 List.First({},"a")="a" List.First(源[數字])=1 List.First(列表[數字])=12. 獲取列表前N個 List.FirstN (list as list , countOrCoundition as any ) as any 獲取列表的前N個數據，第2參數可以為數字也可以為條件。如果為指定條件，則提取的數據為直到不滿足條件前的數據。 例： List.FirstN({1..10}, 2)={1,2} List.FirstN(源[數字], each _<4)={1,2,3} 解釋： 源[數字] 這個是在原查詢中引用列表的格式。此外涉及到列表，所以在進行操作比較的時候需要用到each，_代表的是列表中的每一個值，所以用列表中從頭開始的每一個值去和<4進行比較，直到不滿足並返回匹配條件的幾個值。List.FirstN(列表[數字], each _=2)={} 解釋： 列表[數字] 這個是在其他查詢中引用列表的格式。因為數字是從1開始，1<>2，獲取的是第一個不滿足條件之前的數據，第一個就不滿足，所以結果為空列表(二)從尾開始提取 1. 獲取列表最後一個 List.Last (list as list , optional defaultValue as any ) as any 返回列表的最後1個值，參數2代表如果列表為空，可以指定返回一個值。 List.Last({1..10})=10 List.Last({},"a")="a" List.Last(源[數字])=10 List.Last(列表[數字])=10 2. 獲取列表最後N個 List.LastN (list as list , countOrCoundition as any ) as any 獲取列表的最後N個數據，第2參數可以為數字也可以為條件。如果為指定條件，則提取的數據為直到不滿足條件後的數據。 例： List.LastN({1..10}, 2)={9,10} List.LastN(源[數字], each _>7)={8,9,10} 解釋： 源[數字] 這個是在其他查詢中引用列表的格式。此外涉及到列表，所以在進行操作比較的時候需要用到each，_代表的是列表中的每一個值，所以用列表中末尾開始的每一個值去和>7進行比較，直到不滿足並返回匹配條件的值。List.LastN(列表[數字], each _=9)={} 解釋： 列表[數字] 這個是在其他查詢中引用列表的格式。因為末尾數字是從10開始，不滿足條件，獲取的是第一個不滿足條件之前的數據，第一個就不滿足，所以結果為空列表。(三)根據值提取 1. 獲取列表中最大的數據 List.Max (list as list , optional default as any , optional comparisonCriteria as any , optional includeNulls as nullable logical )as any 返回最大的值，這個值時以Unicode來做比較；第2參數代表如果列表為空，可以指定返回一個值；第3參數代表相反性的排序比較，1代表相反，0代表正常，默認正常；第4參數代表代表返回值是否包含空值，是一個邏輯值true or false。 例： List.Max({1..10})=10 List.Max({},"空值")="空值" 解釋：第2參數是在第1參數為空的時候的返回值，因為第1參數是空值，所以返回第2參數的值。List.Max({1,"a",2,"z","一"})="一" 解釋：根據Unicode值，1<2<"a"<"z"<"一"，所以最大值返回中文的"一"。 List.Max({1..10},null,1)=1 解釋：有第3參數，第3參數代表的是排序。0代表正常，1代表相反，所以返回相反的取值，正常最大值是10，相反則是1，因為第3參數是1代表相反，所以返回的結果是1。List.Max({null},"空值",null,true)=null 解釋：有第4參數，第4參數代表空值的處理，true返回null，false返回第2參數值。這里第4參數是true，所以返回的結果是null。List.Max({null},"空值",null,false)="空值" 解釋：有第4參數，第4參數是false，所以返回第2參數的值。2. 返回列表中最小的數據 List.Min (list as list , optional default as any , optional comparisonCriteria as any , optional includeNulls as nullable logical )as any 返回最小的值，這個值時以Unicode來做比較；第2參數代表如果列表為空，可以指定返回一個值；第3參數代表相反性的排序比較，1代表相反，0代表正常。默認正常；第4參數代表代表返回值是否包含空值計算，是一個邏輯值true or false。 例： List.Min({1..10})=1 List.Min({},"空值")="空值" List.Min({1,"a",2,"z","一"})=1 解釋：因為根據Unicode排序1<2<"a"<"z"<"一"，所以最小值為1。List.Min({1..10},null,1)=10 解釋：因為第3參數為1代表相反，最小的值相反計算就是最大的值，所以結果是10。List.Min({null},"空值",null,true)=null 解釋：第1參數是空值，第2參數是空值返回的值，第3參數是默認值，第4參數是true，代表null作為計算並返回。List.Min({null},"空值",null,false)="空值" 解釋：第1參數是空值，第2參數是空值返回的值，第3參數是默認值，第4參數是false，代表null不作為計算返回，所以返回值為第2參數的"空值"。3. 返回列表中最大的前幾項數據 List.MaxN (list as list , countOrCondition as any, optional comparisonCriteria as any , optional includeNulls as nullable logical )as any 返回Unicode值最大的前幾項；第2參數可以為數字也可以為條件，如果為指定條件，則提取的數據為直到不滿足條件前的數據；第3參數代表排序，1代表從小到大，0代從大到小，默認從大到小；第4參數代表返回值是否包含空值計算，是一個邏輯值true or false。 例： List.MaxN({1..10},3)={10,9,8} 解釋：因為第2參數是數字，所以按第2參數數量提取最大值。List.MaxN({1..10}, each _>6)={10,9,8,7} 解釋：因為第2參數是條件，因為第3參數默認是從大到小排序，也就是把第1參數重新排序後成為{10,9,8,7,6,5,4,3,2,1}。所以排序後的每一個值和第2參數的條件比，直到不滿足為止，這樣比較的話到第5個數6不滿足條件，所以終止後返回的結果就是{10,9,8,7}。List.MaxN({1..10}, each _>7,1)={} 解釋：第2參數是條件，第3參數是1代表從小到大排序，所以在第一個數值1不滿足條件的時候就結束，所以結果為空列表。List.MaxN({1..10},3,1)={1,2,3} 解釋：第2參數是數字，第3參數1表示的是從小到大排序的意思，所以提取的是最小的3項數值，也就是{1,2,3}。List.MaxN({1,null,"a"},3,null,true)={"a",1,null} 解釋：第2參數是數字，第3參數默認值是從大到小，第4參數代表null值也做比較返回，Unicode字元排序順序是英文字元>數字>null，所以排序是按從大到小返回結果也就是{"a",1,null}List.MaxN({1,null,"a"},3,1,false)={1,"a"} 解釋：第2參數是數字，第3參數是1代表從小到大排序，第4參數是false，代表返回值不包含null，Unicode字元排序順序是英文字元>數字>null，所以除去null返回從小到大的前3位排序{1,"a"}。 List.MaxN({1,3,2,null,5}, each _<2,1,true)=Error 解釋：第2參數是條件，第3參數是1代表從小到大排序，第4參數是true代表null值計算並做返回。{null,1,2,3,5}和第3參數條件比，但是null無法進行數字表達式比較，所以返回錯誤。List.MaxN({1,3,2,null,5}, each _<3,1,false)={1,2} 解釋：第2參數是條件，第3參數是1代表從小到大排序，第4參數是false代表null值不做計算返回。{null,1,2,3,5}和第3參數條件比，因為null不做比較，所以實際比較的值時{1,2,3,5}和第2參數比較，直到數字3不滿足條件返回，結果就是{1,2}。4. 返回列表中最小的前幾項數據 List.MinN (list as list , countOrCondition as any, optional comparisonCriteria as any , optional includeNulls as nullable logical )as any 返回Unicode值最小的前幾項；第2參數可以為數字也可以為條件，如果為指定條件，則提取的數據為直到不滿足條件前的數據；第3參數代表排序後的比較，1代表從大到小排序，0代表從小到大排序，默認從小到大排序；第4參數代表代表返回值是否包含空值計算，是一個邏輯值true or false。 例： List.MinN({1..10},3)={1,2,3} 解釋：因為第2參數是數字，所以返回的是最小的3個值，第3參數默認值是從小到大排序，所以返回{1,2,3}。List.MinN({1..10}, each _ <4)={1,2,3} 解釋：因為第2參數是條件，所以比較直到不滿足為止返回，第3參數默認值是從小到大排序，所以在第4個數字4的時候不滿住，所以返回{1,2,3}。List.MinN({1..10}, each _>7,1)={10,9,8} 解釋：因為第2參數是條件，第3參數是1代表從大到小排序，排序後的列表是{10,9,8,7,6,5,4,3,2,1}，所以在7不滿足條件的時候就結束，所以結果為7之前的數據{10,9,8}。List.MinN({1..10},3,1)={10,9,8} 解釋：第2參數是數字，所以直接返回最小的3個值。第3參數是1代表從大到小排序，所以結果是{10,9,8}。List.MinN({1,null,"a"},3,null,true)={null,1,"a"} 解釋：第2參數是數字，代表返回最小的3個值。第3參數是默認值代表從小到大排序，第4參數是true代表null計算並返回。因為Unicode值排序大小是null<1<"a"，所以返回的值時{null,1,"a"}。List.MinN({1,null,"a"},3,1,false)={"a",1} 解釋：第2參數是數字，則返回最小的3項值，第3參數是1代表從大到小排序，第4參數是false，所以null不做比較返回。因為Unicode值排序大小是null<1<"a"，所以從大到小排序的結果是{"a",1,null}，因為null不做比較返回，所以返回結果是{"a",1}。List.MinN({1,null,"a"}, each_>0, 0,false)=Error 解釋：第2參數是條件，第3參數是0代表從小到大排序，第4參數是false，所以null不做比較返回。Unicode大小為 null<1<"a"，實際上需要和第2參數比較的值為{1,"a"}，因為裡面有"a"，所以不能用於數字表達式計算，所以返回錯誤。List.MinN({1,null,"a"}, each _=1,1,false)={} 解釋：第2參數是條件，第3參數是1代表從大到小排序，第4參數是false，所以null不進行計算返回。Unicode大小為 null<1<"a"，實際需要比較的值為{"a",1}，因為第一個值"a"不符合第2參數的條件，所以結束匹配，返回的是空值列表{}。5. 返回列表中的中位數 List.Median (list as list , optional comparisonCriteria as any ) as an y 是對Unicode進行大小比較，返回list的中位數，如果列表包含null值，則在計算時忽略；如果null是唯一值，則返回null；如果列表數據為偶數，則返回較小值；如果列表為偶數，而且都是數值（包括時間日期等可轉換數值），則返回2個的平均值。 例： List.Median({1,2,3})=2 List.Median({})=null List.Median({1,"a","一"})="a" 解釋：根據Unicode值大小1<"a"<"一"，中值為"a"。List.Median({1,"a",null,2,"b"})=2 解釋：因為null不是唯一值，所以計算是忽略，此時中位數有2位，分別是2和"a"，返回較小值，所以是2。List.Median({1,2,3,4})=2.5 解釋：因為列表中都是數值，此時中位數有2位，分別是2和3，所以去平均值為2.5 List.Median({#datetime(2018,11,11,12,0,0),#datetime(2018,11,11,13,0,0)})=2018/11/11 星期日 下午 12:30:00⑶ Excel數據建模——提高Power Query數據處理效率
我們使用Excel中強大的數據處理工具——Power Query進行數據清理與整合，可以處理大量不同來源的數據。在大數據的形勢下，數據量大，數據結構復雜，因此注意建立查詢的方法技巧，可以幫助我們提高Power Query的工作效率。下面介紹我使用Power Query處理數據的一些小技巧。
關於 數據源 ，我喜歡把同類或相關的數據源都放到一個文件夾里，然後從文件夾導入數據。這樣，不但有利於數據源的集中管理，而且當增加新的數據源，或者數據源版本發生變化時，也更方便對查詢進行更新修改。
在Power Query編輯器中處理數據時，盡量 減少重復操作 ，這樣有利於提高數據處理效率。比如，如果多個不同的查詢中有很多步驟是重復的，那麼我們可以先針對這些重復的部分建立一個單獨的查詢，然後再引用這個查詢來建立其他的查詢。
例如，我在上一篇文章中（ Excel數據建模——Power Query數據清理與整合 ），先建立「Worksheet篩選」查詢，以此作為橋梁，然後在引用這個查詢的基礎上，再建立「人員投入」、「人員產出」查詢。通過對先建查詢的引用，減少了重復操作，提高了數據處理的效率。
⑷ EXCEL powerpivot 有沒有人用過
powerQuery最新功能GET
《Power Query新特性：你給答案，我來計算 - 知乎專欄》
相信讀完這篇文章後，你肯定會對powerquery有一個比較全面的了解及認識，那麼powerquery到底能做什麼呢，這里再給大家一些實例：
《用PowerQuery動態匯總文件夾下的多個Excel文件（支持動態增刪自動更新）-雷公子個人博客》
《數據逆透視-多維數據轉回一維 | Excel120》
《PQ：一個快速創建整數序列及字元序列的方法 | Excel120》
《PowerQuery處理典型的中國式二維表格轉一維》
《(視頻)URL分析之參數批量快速提取之Url.Parts函數 - 知乎專欄》
《利用Power Query進行采購發票校驗》
《PowerQuery語言M函數中文翻譯文檔面世 - 知乎專欄》
《查看PowerQuery函數的幾種方法》
PowerQuery技巧類文章：
《同一Excel文檔中多個智能表格如何批量添加到PowerQuery - 知乎專欄》
《PowerQuery實現動態查詢（與文件的路徑無關） | Excel120》
《PowerQuery技巧篇--詞根串聯（生成笛卡爾積數列） - 知乎專欄》
《PowerQuery的參數表格用法》
PowerQuery進階的一些知識：
《PQ：多行屬性合並到一個單元格 | Excel120》
《PowerQuery分表函數簡單應用Table.Partition(分表) - 知乎專欄》
《PowerQuery作為ETL系統 - 自助裝載大數據（>100W）最佳實踐》
⑸ 如何在Power Query中獲取數據——表格篇(3)
樣例表格：
之前講了從表頭獲取，那對應的就有從表尾獲取。
(一)從表尾開始提取
1. 獲取表的最後一條記錄
Table.Last (table as table , optional default as any )as any
第1參數是需要操作的表；第2參數是在空表的情況下的賦值；返回的結果如果是非空表則是最後一條記錄，格式是Record格式，如果是空表則是賦值的格式。
例：
Table.Last(數據)=[姓名="王五",成績=80,學科="英語"]
Table.Last(Table.FromRecords({}),{"空表"})={"空表"}
解釋：空表代表沒有記錄的表，但是可以有標題欄位名。Table.FromRecords({})生成的是一個空表，例如Table.FromList({})，Table.FromColumns({})，Table.FromRows({})也是一樣的，生成的是不具備標題欄位的空表；Table.FromValue({})生成的就是一個標題欄位為Value的空表；因為是空表所以返回指定值。
2. 獲取指定條件表的最後幾條記錄
Table.LastN (table as table , countOrCondition as any ) as table
第1參數為操作的表，第2參數為數字或者條件，返回的是一個表的格式。如第2參數是條件，則從尾開始匹配，返回滿足的行，直到不滿足為止。
例：
<> Table.Last(數據)
解釋：因為Table.LastN返回的是table格式，而Table.Last返回的是record格式，所以不相等。
解釋：因為成績是降序排列，所以返回的結果是直到不滿足條件結束。
Table.LastN(數據,each_[成績]>90)= #table({},{})
解釋：因為最後一條記錄是80，不滿足第2參數的條件，所以沒有滿足條件的數據，返回的結果就是一個空表。
解釋：查找姓名包含"五"的記錄，並返回形成表格。因為第一條記錄是的姓名是王五，滿足條件，第二條記錄是李四不滿足條件結束，所以只返回最後一條記錄並形成表格。
⑹ power query實現多表聯合時的數據量限制是多少
power query實現多表聯合時的數據量限制是3表以內
PowerQuery是以查詢為主題而存在的。任何非以此主題為前提，用其他技術與之的對比都不合適。
如果你已經是輕松玩轉VBA的」EXCEL高手」，這個工具將是一個極好的補充，可以替代大量編碼時間，另外如果投資時間學習M語言（PowerQuery內置編程語言）將推開一個新的大門。如果你是Excel初學者，需要向Excel導入數據而苦思怎麼辦，那PowerQuery正是你的最佳選擇，沒有之一。
⑺ 如何在Power Query中提取數據——數值篇
我們了解到在Power Query中提取數據會因為數據格式不同而提取的方法不同。
前我們講了數據提取的文本篇，這次我們來看下數值格式的數據如何進行提取。
數值的提取主要是進位的問題。在Power Query中對於進位有著比Excel更多的選擇。
1. 向下捨去（類似Excel中的Rounddown）
Number.RoundDown(number as nullable number , optional digits as nullable number ) as nullable number
第1個參數是需要進行操作的數值，第2個參數是進位到哪裡。
例：
Number.RoundDown(123.123,1)=123.1
Number.RoundDown(123.123,-1)=120
Number.RoundDown(123.123,-3)=0
Number.RoundDown(-123.123,1)=-123.2
Number.RoundDown(-123.123,-3)=-1000
注意： 第2參數代表小數的位數，如果是負數則是小數點的前方，如果是正數則是小數點的後方，0則為去掉小數點。同時我們需要注意如果是負數，向下捨去的意義實際上是舍入到小的值。
2. 向上進位（類似Excel中的Rounp）
Number.RoundUp (number as nullable number , optional digits as nullable number ) as nullable number
例：
Number.RoundUp(123.123,1)=123.2
Number.RoundUp(123.123,-1)=130
Number.RoundUp(123.123,-3)=1000
Number.RoundUp(-123.123,1)=-123.1
Number.RoundUp(-123.123,-3)=0
注意： 和Number.RoundDown的區別，在於進位，所以始終是會往大的數值靠。
3. 向0進位（這個在Excel中目前沒有對應函數）
Number.RoundTowardZero (number as nullable number , optional digits as nullable number ) as nullable number
向0進位是什麼意思呢？ 正數往下舍入，負數往上舍入
Number.RoundTowardZero(123.123,1)=123.1
Number.RoundTowardZero(-123.123,1)=-123.1
Number.RoundTowardZero(123.123,-3)=0
Number.RoundTowardZero(-123.123,-3)=0
Number.RoundAwayFromZero(number as nullable number , optional digits as nullable number ) as nullable number
遠離0進位是什麼意思呢？ 正數往上舍入，負數往下舍入
Number.RoundAwayFromZero(123.123,1)=123.2
Number.RoundAwayFromZero(-123.123,1)=-123.2
Number.RoundAwayFromZero(123.123,-3)=1000
Number.RoundAwayFromZero(-123.123,-3)=-1000
4. 四捨五入（類似Excel的Round，但有些許區別）
Number.Round (number as nullable number , optional digits as nullable number , optional roundingMode as nullable RoundingMode.Type ) as nullable number
此函數比Excel裡面多一個參數，一共有3個參數，第三個參數是針對如果碰到數字5如何進行處理，0代表向上進位，1代表向下捨去。默認參數是為1，所以如果要實現真正意義上的四捨五入則需要添加第三參數， 正數為0，負數為1
Number.Round(123.125,2,1)=123.12
Number.Round(123.125,2,0)=123.13
Number.Round(-123.125,2,1)=-123.13
Number.Round(-123.125,2,0)=-123.12
⑻ Power Query中Excel數據的導入介紹
我們在日常工作中，Excel幾乎是每個工作的人員都會操作的軟體之一，甚至比郵箱，Word，PPT等都多。畢竟在大數據時代，數據就是一切，處理數據最基礎的也就是Excel。
我們來看下如何進行Excel數據的導入。
在說Excel導入的時候，我們經常用到的案例都是從表格導入。
在 從表格導入 之前，我們先了解下，什麼是表格，Power Query中是如何定義表格的。
我們來看下，這兩個表格有什麼區別沒？
表1代表的是Excel裡面的超級表，Power Query中的從表格導入的表格；表2代表的就是Excel裡面的普通表格。最明顯的區別，在表1的C5單元格的右下角有一個標記，滑鼠移動到上面會改變形狀。同時滑鼠點擊表1內任意單元格，會出現「設計」菜單，同時會顯示表名稱。
表1這種表格是通過插入表格或者Ctrl+T來獲得，表名稱可以更改，表的大小范圍可以調整，同時還可以使用切片器和快速匯總等功能。通常在我們原本普通的表格中插入後，會自動分配到一個表格樣式，這樣我們就能很清晰的看清楚表格的范圍。
直接從表格導入後，Power Query會自動辨別數據類型並幫你做歸類，這樣就會出現2個步驟， 源 和 更改的類型 。
我們點擊第一步的源，可以發現其使用的函數公式為：
Excel.CurrentWorkbook(){[Name="數據"]}[Content]
Excel.CurrentWorkbook()返回的是當前工作簿內的Power Query認為的表格，然後通過引用的方式獲得表格內容。引用的方式可以參考 Power Query基礎概念（更新）
從表格導入我們基本可以理解了。那我們在來看下 從工作簿導入 。
這幾個都是非隱藏的內容。如果有隱藏的數據則在這一步就顯示不出。如果我們需要同時導入多個內容，可以勾選上面的 選擇多項 。
如果需要自定義的篩選，則可以直接選中文件夾，並點擊編輯即可。此時就會出現一些之前未顯示出來的隱藏文件。
我們可以通過篩選，選擇我們所需要的數據，點擊Data列中的Table值就可以了。
如果我們需要把多個數據源匯總到一個表內，則只需要在篩選後點擊Data右邊的展開按鈕
即可。 需要注意的是名稱的話是默認沒有標題欄位的，所以默認的標題都是Column+序號，例如Column1，Column2，Column3等 。
從文件夾導入
Power Query可以通過文件夾批量導入所需要的文件。需要注意的是默認情況下是會含子目錄。通常會有一個合並和編輯及編輯2個選項，而我們一般直接選擇編輯，因為如果選擇了合並和編輯的話，會導入很多我們不想要的數據，大家也可以試試如果選擇合並和編輯後會導入哪些內容。
點擊編輯後我們會看到整個文件夾裡面的文件，我們可以通過篩選，把我們需要的excel文件給挑選出來， 請注意帶有~的文件名稱代表我們當前正在打開著的文件名稱，這一部分需要過濾篩選掉，要不然之後解析文件的時候會出錯，當然如果你直接把打開的文檔關閉掉，就不會顯示這個信息了 。
到這一步的話，因為數據還沒有被解析出來，還是一個Binary二進制格式，此時我們需要通過增加列，輸入解析Excel的函數Excel.Workbook來處理。（這里有個小技巧，如果在這個函數的第二參數填寫 true（注意小寫） 的時候，就能直接自動辨認數據里的標題行）
解析完成後，我們可以看到自定義列裡面格式變成了Table，每一行Table裡面的這個數據相當於代表我們導入了一個工作簿方式。之後的方法和導入工作簿一樣的操作。
當然在此過程中會產生很多欄位列，而我們最主要的內容類則是在Content欄位裡面，所以我們可以把我們不需要的列都刪除，這樣也顯得導入的數據比較干凈。具體案例可以參考 如何自製便捷的文件管理器？
⑼ EXCELBI中的PowerQuery具有超激情大的數據採集功能，他可以採集以下數據：（多選）
可選：B、C、E
⑽ Power query 限制大下,數據被截斷怎麼處理
刪除不需要的列來釋放內存空間。
每個最高256MB內存限制。
PowerQuery在Excel中通過簡化數據發現、訪問和合作的操作，從而增強了商業智能自助服務體驗。