Forum Discussion

abaken's avatar
abaken
Regular Visitor
3 years ago

How to a filter A Select command

Hi

 

I have the following code in a Powerbi query.  How do I change it to select a date range ? Thank YOu 

 

Allister

 

let
Source = GetConsolidatedSQLData("Select accountcode, branchcode, departmentcode, transactiondate, transactionamount, periodno, yearno, periodid from GLTRAN Where transactiodate =31/07/22"),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"AccountCode", type text}, {"BranchCode", type text}, {"DepartmentCode", type text}, {"PeriodNo", type text}, {"YearNo", type text}, {"PeriodID", type text},

 

{"CompanyCode", type text}, {"TransactionAmount", Currency.Type}, {"TransactionDate", type date}})

in
#"Changed Type"

Need Help

2 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    3 years ago

    in the SQL you have hardcoded single date, so I assume you need to change the SQL WHERE condition. What is the definition of the GetConsolidatedSQLData measure? It can have some additional logic regarding the date too

  • abaken's avatar
    abaken
    Regular Visitor
    3 years ago

    Hi

     

    The code for GetConsolidatedSQLData is below.

     

    I want to leave this code alone if I can as it is used by several queries some of which do not require a date range filter.

     

    How Can I do this ?

    Thank You

     

    Allister

     

    let
    Source = (sqlstmt as text) => let

    AccData1= GetSQLData(sqlstmt,"NLD"),
    AccData1c = Table.AddColumn(AccredoData1,"CompanyCode", each "NLD"),

    AccData2= GetSQLData(sqlstmt,"AKL"),
    AccData2c = Table.AddColumn(AccredoData2,"CompanyCode", each "AKL"),


    AccData = Table.Combine({
    AccData1c
    ,AccData2c

    })
    in
    AccredoData
    in
    Source