Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
User232431
Helper III
Helper III

Power query to get last n values

Hi I am trying to get last n values from query.

My "Period_f" parameter is in form of ex: 202403 (passing through slicer with Bind parameter) ,  now if i wanted to select data between 202311 to 202403 the below query gives data only from 202401-202403 .Prior to 202401 are missing due to 5 points difference(ex: if i select 202403 the max its going upto 202397) . Problem will not arrive if select 202408 
How to modify below query to pass list of values like if select 202403 then the list will be (202403,202402,202401,202312,202311) 

 

let
filtered_query = " select ""Location"" , ""Frequency"" , ""Period"" , ""Value"" from sample.raw_data
where ""Location"" ='"&Location_f&"' and
""Frequency"" ='"&Frequency_f&"' and
""Period"" >"& Text.From( Value.Subtract( Period_f,5))&" and
""Period"" <="& Text.From( Period_f)&"
",
Source = Value.NativeQuery(PostgreSQL.Database("localhost", "sample"), filtered_query, null, [EnableFolding=true]),
#"Filtered Rows" = Table.SelectRows(Source, each [Location] = Location_f),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Frequency] = Frequency_f)
in
#"Filtered Rows1"

 

Thanks in advance

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Use Date.AddMonths instead.

 

lbendlin_0-1730818353639.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjEwVoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.ToText(Date.AddMonths(Date.FromText(Text.From([Column1]) & "01",[Format="yyyyMMdd"]),-5),[Format="yyyyMM"]))
in
    #"Added Custom"

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @User232431 ,

Thanks for Sahir_Maharaj's and lbendlin's replies!

And @User232431 , have you solved your problem? If solved please mark the reply in this post which you think is helpful as a solution to help more others facing the same problem to find a solution quickly, thank you very much!

Best Regards,
Dino Tao

Sahir_Maharaj
Super User
Super User

Hello @User232431,

 

Can you please try this approach:

let
    Period_f = 202403,   
    PreviousPeriods = 5, 

    PeriodList = List.Generate(
        () => Period_f,
        each _ >= 202311,  
        each if Text.End(Text.From(_), 2) = "01" then _ - 89 else _ - 1
    ),

    PeriodListString = Text.Combine(List.Transform(PeriodList, each """" & Text.From(_) & """"), ", "),
    
    filtered_query = "SELECT ""Location"", ""Frequency"", ""Period"", ""Value"" FROM sample.raw_data
                      WHERE ""Location"" = '" & Location_f & "'
                        AND ""Frequency"" = '" & Frequency_f & "'
                        AND ""Period"" IN (" & PeriodListString & ")",

    Source = Value.NativeQuery(PostgreSQL.Database("localhost", "sample"), filtered_query, null, [EnableFolding=true]),

    #"Filtered Rows" = Table.SelectRows(Source, each [Location] = Location_f),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Frequency] = Frequency_f)
in
    #"Filtered Rows1"

 


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
lbendlin
Super User
Super User

Use Date.AddMonths instead.

 

lbendlin_0-1730818353639.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjEwVoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.ToText(Date.AddMonths(Date.FromText(Text.From([Column1]) & "01",[Format="yyyyMMdd"]),-5),[Format="yyyyMM"]))
in
    #"Added Custom"

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.