Power Query冷知識

-

  1.  高版本的exel(如365版)製作的Power  Query如果在低版本excel(如2007版)中不能看到Power Query，於低版本改過excel後存檔再回到高版本中打開，原本編輯的PQ仍然存在。(365版製作的PQ於2007版excel中修改過工作表存檔，於365開啟時仍能看到PQ查詢表)-2021/12/18

留言

張貼留言

這個網誌中的熱門文章

在excel活頁簿中尋找外部連結

-
圖片
                    有時候我們在各個excel工作表中設了連結其他excel檔中的儲存格來源，但久了就忘了有哪些儲存格有使用外部連結，這時候可以使用搜尋xl的方式，找出有使用外部連結的儲存格。          下圖就是一開啟檔案會跳出的提示，這時建議先不更新，否則原本有的結果可能會因為找不到連結出現錯誤。          接下來按Ctrl+F搜尋，找出活業簿中所有含有xl字眼的儲存格一個一個確認 。 按全部搜尋後就會出現所有連結儲存格位置，可以逐一檢視。
閱讀完整內容

Power Query_00查詢很慢的原因_重複查詢

-
圖片
            最近執行Power Query整理較多重查詢時發生明明檔案只有10幾M結果程式在跑時出現幾百M的狀況，原本應該很快的查詢反而花了幾分鐘以上，後來在官方說明發現原因了，在同一個檔案裡的來源(可能幾M~幾十M)經過不同查詢表多次引用來源，產生不同查詢結果，不同的結果可能又互相引用，這種情況下會造成查詢一直重複，而且會針對源頭資料一直跑，如果電腦不夠強可能就會當掉。(在初學Power Query時最好養成常常存檔，避免檔案當掉，步驟沒存到)           我自己的解決方式是將源頭，還有部分查詢會被多次引用的查詢表另外存一個檔，這樣這部分的查詢不會在每次重新整理時再跑一次。 官方的資料標題是 參考 Power Query 查詢 ，下面是官方說明資料: 請考慮數個查詢： Query1  的資料來源是 Web 服務，且其載入已停用。  Query2 、 Query3  與  Query4  都參考  Query1 ，且其輸出會載入至資料模型。 Power Query多重查詢變慢原因說明          當資料模型重新整理時，通常會假設 Power Query 是擷取 Query1 的結果，且參考查詢會重複使用它。 此想法不正確 。 事實上， Power Query 會分別執行 Query2、Query3 與 Query4 。 您可以想成 Query1 步驟內嵌在 Query2 中。 Query3 與 Query4 的情況也是如此。 下列圖表呈現更清楚的查詢執行方式。 Power Query多重查詢變慢原因說明(拆解)        Query1 會執行三次。 多次執行可能會導致資料重新整理緩慢，並對資料來源造成負面影響。         在 Query1 中使用 Table.Buffer 函式，不會消除額外資料擷取。 此函式會在記憶體緩衝資料表。 而且，已緩衝的資料表只能在相同查詢執行中使用。 因此，在範例中，如果在執行 Query2 時緩衝 Quer...
閱讀完整內容

VBA_執行錯誤6溢位

-
        最近請資訊同事幫忙寫一個VBA撈資料庫的資料，因為我要跑的資料大約9萬多筆，同事幫我弄 完後跟我說只能顯示32,767筆，這我還是第一次遇到因為 office 2003版的限制是65,536筆，之後的版本已經能達到1,048,576筆。         後來我上網搜尋瞭一下出現的錯誤資訊( 執行錯誤6溢位 )，剛好有提到關於32,767筆的，應該是把資料型態設為 Integer ，因為代碼牽涉到資料庫密碼，因此我也看不到相關代碼，跟同事確認後的確是這設定問題，我請同事改為 Long 筆數完全不會受限甚至超過1,048,576筆，long 的範圍值 從-9223372036854775808 到9223372036854775807（9.2 ... E + 18）的值。 請詳官方介紹 long資料類型  。 下面的代碼會出現 錯誤資訊( 執行錯誤6溢位 ) sub TEST_A() Dim r As Integer For r = 1 To 100000 Next End Sub 下面的代碼執行時不會出現錯誤   Sub TEST_B() Dim r As Long For r = 1 To 100000 Next End Sub
閱讀完整內容