用GPT修改M語法_以日期列處理

在2023年GPT開始使用時我就詢問過GPT，可能當時的GPT還不夠聰明，給予的答案無法運行，我認為原始的語法應該可以更聰明點，詢問過Power BI社群的人，但沒有人提供答案，終於在現在再度詢問GPT給予的答案與我思考的方向依樣，只是我懂得語法不多，透過GPT幫忙解答，我也學到了新知識。

下面是GPT提供目前最優的版本

let

    // 定义日期和参数

    起始日期 = "2023/12/29",

    最新日期 = "2024/6/7",

    周前日期 = "2024/5/31",

    日期列表 = {"2023/12/29", "2024/5/10", "2024/5/17", "2024/5/24", "2024/5/31", "2024/6/7"},

    趴數 = 0.05, // 例如5%

 

    // 数据处理

    來源 = Table.NestedJoin(投資組合, {"持股標的"}, 資料_5周, {"標的"}, "資料_5周", JoinKind.RightOuter),

    已展開資料 = Table.ExpandTableColumn(來源, "資料_5周", List.Combine({{"標的"}, 日期列表})),

    已排序資料列 = Table.Sort(已展開資料, {{"持股標的", Order.Descending}}),

    已移除資料行 = Table.RemoveColumns(已排序資料列, {"持股標的"}),

    加入淨值 = Table.AddColumn(已移除資料行, "淨值", each [股數] * [匯率] * Record.Field(_, 最新日期)),

    Base_淨值總額 = List.Sum(加入淨值[淨值]),

    加入年度漲幅 = Table.AddColumn(加入淨值, "年度漲幅", each (Record.Field(_, 最新日期) - Record.Field(_, 起始日期)) / Record.Field(_, 起始日期), Percentage.Type),

    加入周漲幅 = Table.AddColumn(加入年度漲幅, "周漲幅", each (Record.Field(_, 最新日期) - Record.Field(_, 周前日期)) / Record.Field(_, 周前日期), Percentage.Type),

    加入淨值比 = Table.AddColumn(加入周漲幅, "淨值比", each [淨值] / Base_淨值總額, Percentage.Type),

    移除多餘列 = Table.RemoveColumns(加入淨值比, {"股數", "匯率", "淨值"}),

    重新排列列 = Table.ReorderColumns(移除多餘列, List.Combine({{"標的", "淨值比"}, 日期列表, {"周漲幅", "年度漲幅"}})),

    排序及篩選 = Table.SelectRows(

        Table.Sort(重新排列列, {{"淨值比", Order.Descending}, {"標的", Order.Descending}}),

        each (List.Contains(投資組合[持股標的], [標的]) or Number.Abs([周漲幅]) >= 趴數 or List.Contains(指數, [標的])) and not List.Contains(排除, [標的])

    )

in

    排序及篩選

下面是GPT提供減少手動版本

我只是將語法丟出，GPT自動提供簡易減少手動版本

let

    來源 = Table.NestedJoin(投資組合, {"持股標的"}, 資料_5周, {"標的"}, "資料_5周", JoinKind.RightOuter),

    #"已展開 資料_5周____改" = Table.ExpandTableColumn(來源, "資料_5周", {"標的","2023/12/29","2024/5/10","2024/5/17", "2024/5/24","2024/5/31","2024/6/7"}),

    已排序資料列 = Table.Sort(#"已展開 資料_5周____改",{{"持股標的", Order.Descending}}),

    已移除資料行 = Table.RemoveColumns(已排序資料列,{"持股標的"}),

 

    // 獲取所有日期欄位名稱

    日期欄位名稱 = List.RemoveFirstN(List.RemoveItems(Table.ColumnNames(已移除資料行), {"標的", "股數", "匯率"}), 0),

    最後一欄 = List.LastN(日期欄位名稱, 1){0},

    最後第二欄 = List.LastN(日期欄位名稱, 2){0},

   

    // 計算淨值

    T淨值_______改 = Table.AddColumn(已移除資料行, "淨值", each [股數]*[匯率]*Record.Field(_, 最後一欄)),

    Base_淨值總額 = List.Sum(T淨值_______改[淨值]),

 

    // 計算年度漲幅和周漲幅

    T年度漲幅_____改 = Table.TransformColumnTypes(

        Table.AddColumn(T淨值_______改, "年度漲幅", each (Record.Field(_, 最後一欄) - Record.Field(_, "2023/12/29")) / Record.Field(_, "2023/12/29")),

        {{"年度漲幅", Percentage.Type}}

    ),

    T周漲幅_____改 = Table.TransformColumnTypes(

        Table.AddColumn(T年度漲幅_____改, "周漲幅", each (Record.Field(_, 最後一欄) - Record.Field(_, 最後第二欄)) / Record.Field(_, 最後第二欄)),

        {{"周漲幅", Percentage.Type}}

    ),

   

    // 計算淨值比

    T淨值比 = Table.TransformColumnTypes(

        Table.AddColumn(T周漲幅_____改, "淨值比", each [淨值] / Base_淨值總額),

        {{"淨值比", Percentage.Type}}

    ),

   

    // 移除不必要的欄位

    已移除資料行1 = Table.RemoveColumns(T淨值比, {"股數", "匯率", "淨值"}),

 

    // 重新排列欄位順序

    重新排_____改 = Table.ReorderColumns(已移除資料行1, {"標的", "淨值比"} & 日期欄位名稱 & {"周漲幅", "年度漲幅"}),

 

    // 排序及篩選資料

    T排序及篩選 = Table.SelectRows(

        Table.Sort(重新排_____改, {{"淨值比", Order.Descending}, {"標的", Order.Descending}}),

        each (List.Contains(投資組合[持股標的], [標的]) = true or Number.Abs([周漲幅]) >= 趴數 or List.Contains(指數, [標的]) = true) and List.Contains(排除, [標的]) = false

    ),

  

in

    T排序及篩選

修改前的版本

let

    來源 = Table.NestedJoin(投資組合, {"持股標的"}, 資料_5周, {"標的"}, "資料_5周", JoinKind.RightOuter),

    #"已展開 資料_5周____改" = Table.ExpandTableColumn(來源, "資料_5周", {"標的","2023/12/29","2024/5/10","2024/5/17", "2024/5/24","2024/5/31","2024/6/7"}),

    已排序資料列 = Table.Sort(#"已展開 資料_5周____改",{{"持股標的", Order.Descending}}),

    已移除資料行 = Table.RemoveColumns(已排序資料列,{"持股標的"}),

    T淨值_______改 = Table.AddColumn(已移除資料行, "淨值", each [股數]*[匯率]*[#"2024/6/7"]), //這裡也要修改........

    Base_淨值總額 = List.Sum(T淨值_______改[淨值]),

    T年度漲幅_____改 = Table.TransformColumnTypes(

                 Table.AddColumn(T淨值_______改, "年度漲幅", each ([#"2024/6/7"]-[#"2023/12/29"])/[#"2023/12/29"]) , //年度如果修改要去改 資料_5週那個進階查詢

                         {{"年度漲幅", Percentage.Type}}),

    T周漲幅_____改 = Table.TransformColumnTypes(

                 Table.AddColumn(T年度漲幅_____改, "周漲幅", each ([#"2024/6/7"]-[#"2024/5/31"])/[#"2024/5/31"]) ,  //這裡也要改

                       {{"周漲幅", Percentage.Type}}),

    T淨值比 = Table.TransformColumnTypes(Table.AddColumn(T周漲幅_____改, "淨值比", each [淨值]/Base_淨值總額),{{"淨值比", Percentage.Type}}),

    已移除資料行1 = Table.RemoveColumns(T淨值比,{"股數", "匯率","淨值"}),

    重新排_____改 = Table.ReorderColumns(已移除資料行1,{"標的", "淨值比","2023/12/29","2024/5/10","2024/5/17", "2024/5/24","2024/5/31","2024/6/7","周漲幅", "年度漲幅"}),

    T排序及篩選 = Table.SelectRows(

                    Table.Sort(重新排_____改,{{"淨值比", Order.Descending}, {"標的", Order.Descending}}) , each (List.Contains(投資組合[持股標的],[標的])=true or Number.Abs([周漲幅])>=趴數 or List.Contains(指數,[標的])=true) and List.Contains(排除,[標的])=false   )

in

T排序及篩選

 

Power Query_避免重複查詢_Power BI desktop版

 

• 如果沒有強大的電腦資源，處理太大量資料
• 避免重複查詢(Desktop版無法像excel版分檔處理)

        大約10月中在現在的公司開始在使用Power Query，因為excel版本不夠，所以使用desktop版，不過在使用上發生了瑕疵，筆電的資源不足，跑10萬筆以上資料就開始會鈍，在假日時好不容易花了1小時用了複雜查詢，在存檔時也花了1小時，一度要放棄存檔...

       有一句名言當你得不到你想的到的東西時，你會得到經驗，這次我真的是得到經驗了，我花了點時間，將資料比較多的部分改成用SQL擷取，而且是分段處理，因為我在用SQL時如果用比較複雜的子查詢也是卡住，人生不該把時間放在等待...

Power Query 久沒用 會退化

         做個簡單紀錄，到新公司大約5個半月，因為新公司office版本不支援PQ，所以PQ也超過3個月沒寫，現在只有每週投資紀錄的修改，突然想要把一個資料排除時，瞬間有點卡住，失智是這種感覺嗎?

之前PQ可以在進階編輯器裡迅速地寫出想要的代碼，現在大概是能改、能用，不禁有種不進步真的是一種很恐怖的感覺，這比手上股票跌還難過，這是一種技能被剝奪的感覺。

Power Query冷知識

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

Power Query_00常用List 清單

下面介紹一下常用清單，日期清單的表達方式比較不一樣，當然表達方式也不是只有一種。(M語言函數大小寫要求很嚴格，須注意該大寫的地方要大寫。)

 

• 日期清單:

List.Dates(    #date(2021,6,28),7,#duration(1, 0, 0, 0)  )  ，從2021/6/28開始逐日遞增1日，建立七個值清單。

List.Dates(   Date.From(DateTime.LocalNow()) ,7,  #duration( 1, 0, 0, 0)  )，從今天開始逐日遞增1日，建立七個值清單。

List.Dates(   Date.From(DateTime.LocalNow()) ,7,  #duration( -1, 0, 0, 0)  ) ，從今天開始逐日遞減1日，建立七個值清單。

• 數字清單：{1..10}，根據輸入開始的數字及結束的數字建立清單，本例與{1,2,3,4,5,6,7,8,9,10}結果依樣。

• 文字格式數字清單:{"1".."10"},與{"1","2","3","4","5","6","7","8","9","10"} 相同。

• 大寫字母清單:{"A".."Z"} ,建立A~Z 26個大寫字母清單。

• 小寫字母清單:{"a".."z"} ,建立a~z 26個大寫字母清單。

• 所有字母清單:{"A".."z"}，建立包含大小寫的字母清單，與{"A".."Z","a".."z"}結果相同。

• 中文清單:{"一".."龢"} 龢音同和，會產生常用的中文，但並非所有中文字。

Power Query_00關於Buffer(2個月沒寫VBA了)

            對於使用Power Query有時很慢、卡住讓我有點困擾，因為在某種程度我認為Power Query比VBA快，而且在不同資料來源彙整上我還是偏好使用Power Query，研究了官方對於重複查詢的問題(這裡)還有用一些關鍵字(Ppwer Query Slow+ Table.Buffer、 List.Buffer、 Table.NestedJoin、Table.Combine)爬了國外的論壇，在Power Query有些情況變慢大概有些理解，在這裡就做個詳細記錄。

      我本身非資訊背景，因此大部分資訊都是在網路上資源看到，在一些國外論壇或部落客大部分是建議以Table.Buffer及List.Buffer方式處理，但這種方式也不是完全有用，官方的建議(這裡)是建立資料流程封裝來源資料，我的理解是將源頭資料比較大的，還有一些中間步驟有使用到比較複雜查詢時另外存檔，這樣後面的步驟在查詢時就不會讓前面的步驟一直讀取，如果來源資料很大，雖然你已經使用Table.Buffer及List.Buffer處理，他仍然會在讀取一次源頭(我觀察是這樣)，如果沒用Bufferr就會讓讓讀取次數滾很多次。

Buffer 概念簡單說明一下，這是我理解的，所以可能說明不是那麼正確，在Power Query中查詢表中的每一個步驟本身都是一個Table或List，如果某些步驟做了很複雜的查詢，這時下一個步驟要飲用時會建議使用Buffer的方式，讓他不會重新計算一次，而是指接引用Buffer裡面的結果，有大陸的網友以計算機的M功能做Buffer做解釋，感覺是幫助了理解。

          就目前查詢到最常使用就是在Table.Comine 或 Table.NestedJoin之前先將相關的Table以Table.Buffer處理，List.Contains則是先用List.Buffer處理。

        不過最有效的方式就是資料流程的規劃，我有一個對同一資料做不同查詢再互相引用，未將步驟切開另外存檔時跑了20幾分鐘，切開後只要1分鐘內，這真的令人驚訝。在VBA中每一個步驟是很明顯的，但在Power Query不同查詢表的先後順序感覺不出它Run的方式，我想這也是其中一個影響到查詢時間的因素。

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 時緩衝 Query1，則在執行 Query3 與 Query4 時，會無法使用已緩衝的資料。 它們本身會再緩衝資料兩次。 (事實上，此結果可能會造成負面效能惡化，因為每個參考查詢都會緩衝該資料表。)

建議

一般來說，我們建議您參考查詢，以避免在整個查詢中重複邏輯。 不過，如此文章所述，此設計方法可能造成資料重新整理變慢，且使資料來源負擔過重。

我們建議您改為建立資料流程。 使用資料流程可以改善資料重新整理時間，並降低對資料來源的影響。

您可以設計資料流程來封裝來源資料和轉換。 因為資料流程是 Power BI 服務中的持續性資料存放區，所以其資料擷取速度很快。 因此，即使參考查詢導致多個資料流程要求，資料重新整理時間仍可獲得改善。

在此範例中，如果將 Query1 重新設計為資料流程實體，則 Query2、Query3 與 Query4 可以將它作為資料來源使用。 若使用此設計，系統只會評估 Query1 的來源實體一次。