CODEWITHSUNDEEP
sqlms-accesspowerbipowerquery
DafyddNZ
DafyddNZ

Reputation: 258

Converting Access DB macro into PowerBI M Language

I am in the process of converting an Access DB into a Power BI report. The Access Db has a macro that is run from a form using parameters, I have the same parameters in Power BI.

My current Power BI query is set up like this:

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

However, in the Access DB macro, there are these update scripts:

--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]));

The [Forms]![Frm_Menu]![YEAR_2] field is equivalent to the Yr=Year Parameter in the Power BI report, and the [Forms]![Frm_Menu]![PERIOD_2] is the same as the Period=YTDMonth parameter.

Is there a simple clean way to add these update scripts to the Power BI query?

EDIT:

I would like to update the fields P1 - P12 using M Language, in the same way as the Access DB macro update statements do.

Upvotes: 1

Views: 134

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

I can think of two approaches to this.

1. Modify the query statement where you define your P columns.

    , (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. If it needs to be done in M language, you can create custom columns with similar logic.

  #"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),

Once those are created you can delete the original P columns and rename the C columns.

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

Upvotes: 2

Related Questions