Forum Discussion

Pikachu-Power's avatar
Pikachu-Power
Icon for Impactful Individual rankImpactful Individual
5 years ago
Solved

restrict dataset for last year

hello all,

 

i am not very familiar with the power query. in the import modus i load data from 01.01.2011, 01.02.2011... until 01.01.2021. but i want dynamicly start the load from the beginning of the last year: from 01.01.2020 until now/01.01.2021.

 

In SQL it would be:

WHERE DateFirstofTheMonth >= DATEFROMPARTS(YEAR(GETDATE())-1, 1, 1)

 

How can I do it in the Power Query?

 

Thanks

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Pikachu-Power 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Query1:

     

    You may add a new step with the following m codes.

    = Table.SelectRows(#"Renamed Columns",each 
let today = DateTime.LocalNow()
in
[Date]>=#date(Date.Year(today)-1,1,1) and 
[Date]<=Date.From(today)
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

    • Pikachu-Power's avatar
      Pikachu-Power
      Icon for Impactful Individual rankImpactful Individual
      5 years ago

      But how to say start from 01. 01. CurrentYear-1 ?

      Say Last Year is not korrekt: Than i get only values from 2020 without 2021.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        it can be done in several ways, by combining some of the features of the rich battery listed here

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Pikachu-Power 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Query1:

     

    You may add a new step with the following m codes.

    = Table.SelectRows(#"Renamed Columns",each 
let today = DateTime.LocalNow()
in
[Date]>=#date(Date.Year(today)-1,1,1) and 
[Date]<=Date.From(today)
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.