Forum Discussion

User232431's avatar
User232431
Helper III
1 year ago
Solved

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

Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    Use Date.AddMonths instead.

     

    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"

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Use Date.AddMonths instead.

     

    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"
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    1 year ago

    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"

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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