将Access DB宏转换为PowerBI M语言

Question

我正在将Access DB转换为Power报告。Access Db有一个宏，它是从使用参数的表单运行的，在Power中我有相同的参数。

我当前的Power查询设置如下：

let
    PDL=PDLedger,
    PUL=PULedger,
    Period=YTDMonth,
    Yr=Year,
    Source = Sql.Database("server", "database", 
    [Query="SELECT GBAID
                , GBMCU
                , GBOBJ
                , [GBAN01]/100 AS P1
                , [GBAN02]/100 AS P2
                , [GBAN03]/100 AS P3
                , [GBAN04]/100 AS P4
                , [GBAN05]/100 AS P5
                , [GBAN06]/100 AS P6
                , [GBAN07]/100 AS P7
                , [GBAN08]/100 AS P8
                , [GBAN09]/100 AS P9
                , [GBAN10]/100 AS P10
                , [GBAN11]/100 AS P11
                , [GBAN12]/100 AS P12
                , case when GBLT = 'B2'
            or GBLT = 'BU'
            or GBLT = 'BA' 
            then 'B2' else GBLT end as GBLT
                , GBFY
                , GBCO
                , GBSBLT
                , GBSBL
        FROM TableName
        WHERE (((GBOBJ) Between '2000' And '8000') 
        AND ((GBLT)='B2' 
        Or (GBLT)='BA' 
        Or (GBLT)='AA' 
        Or (GBLT)='GP' 
        Or (GBLT)='" &PDL& "')
        AND ((GBFY)='" &Yr& "'))
        OR (((GBOBJ) Between '1000' And '1550') 
        AND ((GBLT)='BU' 
        Or (GBLT)='AU' 
        Or (GBLT)='" &PUL& "')
        AND ((GBFY)='" &Yr& "')
        AND ((GBSBLT)='S' 
        Or (GBSBLT)='P'))
        ORDER BY GBOBJ", 
    CommandTimeout=#duration(0, 0, 10, 0), HierarchicalNavigation=true, MultiSubnetFailover=true])
in
    Source

但是，在Access DB宏中，有以下更新脚本：

--REPORTS_040_ClearActual_TEMP
UPDATE Tbl_FCAST_TEMP 
SET Tbl_FCAST_TEMP.P1 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=1,[P1],0) --period_2 = 2
    , Tbl_FCAST_TEMP.P2 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=2,[P2],0) --period_2 = 2
    , Tbl_FCAST_TEMP.P3 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=3,[P3],0) --period_2 = 2
    , Tbl_FCAST_TEMP.P4 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=4,[P4],0) --period_2 = 2
    , Tbl_FCAST_TEMP.P5 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=5,[P5],0) --period_2 = 2
    , Tbl_FCAST_TEMP.P6 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=6,[P6],0) --period_2 = 2
    , Tbl_FCAST_TEMP.P7 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=7,[P7],0) --period_2 = 2
    , Tbl_FCAST_TEMP.P8 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=8,[P8],0) --period_2 = 2
    , Tbl_FCAST_TEMP.P9 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=9,[P9],0) --period_2 = 2
    , Tbl_FCAST_TEMP.P10 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=10,[P10],0) --period_2 = 2
    , Tbl_FCAST_TEMP.P11 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=11,[P11],0) --period_2 = 2
    , Tbl_FCAST_TEMP.P12 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=12,[P12],0) --period_2 = 2
WHERE (((Tbl_FCAST_TEMP.GBLT)='AA' 
 Or (Tbl_FCAST_TEMP.GBLT)='GP' 
 Or (Tbl_FCAST_TEMP.GBLT)='AU') 
 AND ((Tbl_FCAST_TEMP.GBFY)=[Forms]![Frm_Menu]![YEAR_2])); --17

--REPORTS_042_ClearFcast_TEMP
UPDATE Tbl_FCAST_TEMP 
 SET Tbl_FCAST_TEMP.P1 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=1,0,[P1])
     , Tbl_FCAST_TEMP.P2 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=2,0,[P2])
     , Tbl_FCAST_TEMP.P3 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=3,0,[P3])
     , Tbl_FCAST_TEMP.P4 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=4,0,[P4])
     , Tbl_FCAST_TEMP.P5 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=5,0,[P5])
     , Tbl_FCAST_TEMP.P6 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=6,0,[P6])
     , Tbl_FCAST_TEMP.P7 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=7,0,[P7])
     , Tbl_FCAST_TEMP.P8 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=8,0,[P8])
     , Tbl_FCAST_TEMP.P9 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=9,0,[P9])
     , Tbl_FCAST_TEMP.P10 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=10,0,[P10])
     , Tbl_FCAST_TEMP.P11 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=11,0,[P11])
     , Tbl_FCAST_TEMP.P12 = IIf(([Forms]![Frm_Menu]![PERIOD_2]-1)>=12,0,[P12])
WHERE (((Tbl_FCAST_TEMP.GBLT)<>"AA" 
 And (Tbl_FCAST_TEMP.GBLT)<>"GP" 
 And (Tbl_FCAST_TEMP.GBLT)<>"B2" 
 And (Tbl_FCAST_TEMP.GBLT)<>"AU") 
 AND ((Tbl_FCAST_TEMP.GBFY)=[Forms]![Frm_Menu]![YEAR_2]));

Forms字段等效于Power报告中的Yr=Year参数，表单与Period=YTDMonth参数相同。

是否有一种简单、干净的方法将这些更新脚本添加到Power查询中？

编辑：

我想使用M语言更新字段P1 - P12，就像Access宏update语句一样。

Answer 1

我可以想出两种方法来解决这个问题。

1.修改您定义P列的查询语句。

    , (CASE WHEN " & Yr & " >= 1 THEN [GBAN01]/100 ELSE 0 END) AS P1
    , (CASE WHEN " & Yr & " >= 2 THEN [GBAN01]/100 ELSE 0 END) AS P2
      [...]
    , (CASE WHEN " & Yr & " >= 11 THEN [GBAN11]/100 ELSE 0 END) AS P11
    , (CASE WHEN " & Yr & " >= 12 THEN [GBAN12]/100 ELSE 0 END) AS P12

2.如果需要用M语言完成，可以创建具有类似逻辑的自定义列。

  #"Custom 1" = Table.AddColumn(Source, "C1", each if Yr >= 1 then [P1] else 0),
  #"Custom 2" = Table.AddColumn(#"Custom 1", "C2", each if Yr >= 2 then [P2] else 0),
    [...]
  #"Custom 11" = Table.AddColumn(#"Custom 10", "C11", each if Yr >= 11 then [P11] else 0),
  #"Custom 12" = Table.AddColumn(#"Custom 11", "C12", each if Yr >= 12 then [P12] else 0),

一旦创建了这些列，您就可以删除原始的P列并重命名C列。

  #"Removed Columns" = Table.RemoveColumns(#"Custom 12",{"P1", "P2", [...], "P11", "P12"}),
  #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"C1", "P1"},{"C2", "P2"},[...],{"C11", "P11"},{"C12", "P12"}})