locked
How to use Parameters in Power Pivot Query RRS feed

  • Question

  • Hello,
    Below is the query that will show Purchase History for the hard coded Date filters,

    {L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

    let
        Source = Sql.Database("navsql\navision", "OSI5", [Query="Select #(lf)#(tab)PIL.[Posting Date],#(lf)#(tab)PIL.[Buy-from Vendor No_],#(lf)#(tab)PIH.[Buy-from Vendor Name],#(lf)#(tab)PIH.[Buy-from Country_Region Code],#(lf)#(tab)PIH.[Currency Code],#(lf)#(tab)PIH.[Vendor Posting Group],#(lf)#(tab)PIL.[Document No_],#(lf)#(tab)case #(lf)#(tab)#(tab)when PIL.Type = 1 then 'G/L'#(lf)#(tab)#(tab)when PIL.Type = 2 then 'Item'#(lf)#(tab)#(tab)When PIL.Type = 3 then 'Fixed Asset'#(lf)#(tab)#(tab)when PIL.Type = 4 then 'Charge (Item)'#(lf)#(tab)end as Type,#(lf)#(tab)PIL.[No_],#(lf)#(tab)PIL.Description,#(lf)#(tab)PIL.[Gen_ Prod_ Posting Group] As GPPG,#(lf)#(tab)PIL.[Unit of Measure Code] as UOM,#(lf)#(tab)PIL.Quantity,#(lf)#(tab)PIL.[Unit Cost (LCY)] as [LCY Cost],#(lf)#(tab)(PIL.Quantity * PIL.[Unit Cost (LCY)]) As [LCY Amount],#(lf)#(tab)PIL.[Direct Unit Cost],#(lf)#(tab)(PIL.Quantity * PIL.[Direct Unit Cost]) As [Currency Amount],#(lf)#(tab)PIL.[Manufacturer Code],#(lf)#(tab)PIL.[Mfg_ Part No_],#(lf)#(tab)PIH.[Purchaser Code],#(lf)#(tab)Item.[Buyer]#(lf)#(lf)#(lf)FROM [dbo].[CompanyName$Purch_ Inv_ Line] as PIL#(lf)#(tab)inner Join [dbo].[CompanyName$Purch_ Inv_ Header] as PIH#(lf)#(tab)ON PIL.[Document No_] = PIH.[No_]#(lf)#(tab)left Join [dbo].[CompanyName$Item] as Item#(lf)#(tab)ON PIL.[No_] = Item.[No_]#(lf)where #(lf)#(tab)PIL.[Posting Date] >= fParameters("Parameter_Tab",1) AND #(lf)#(tab)PIL.[Posting Date] <= '2020-10-31' AND #(lf)#(tab)PIL.[Prod_ Order No_] = '' AND #(lf)#(tab)PIL.Quantity <> 0 AND#(lf)#(tab)PIL.[Type] = 2#(lf)"]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Posting Date", type date}})
    in
        #"Changed Type"


    How can i change the hard code date filters to Parameters using excel parameter Table(new table with 2 Columns like From_Date & To_Date)? So, the Users can run this their own by just chaging the Date Values

    ALI

    Thursday, October 8, 2020 7:55 AM

Answers

  • There are quite a few tutorials out there about how to do this. Try searching online for "Power Query parameterize SQL native query".

    Ehren

    Friday, October 9, 2020 9:11 PM

All replies

  • There are quite a few tutorials out there about how to do this. Try searching online for "Power Query parameterize SQL native query".

    Ehren

    Friday, October 9, 2020 9:11 PM
  • Thanks....

    ALI

    Thursday, November 5, 2020 3:51 AM