cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

how to write formula when doing query

 

Hi Profs

 

could you help me on this?

I hope the last 3 months lastest date to be filtered into the query, that is current month lastest date is 0608, last month latest date is 0525, then last two month latest date is 0427, so when new date added into,  it will be 0615, 0525 and 0427.

so will always maintain the latest 3 months, each latest date in that month.

 

how to filter in the query process, I do not want to load in all the data, then use dax to get the result...

the data is too big

here I extract the number as index, after that I am not sure how to do.

 

thanks in advance profs

 

Capture.PNG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III
Super User III

Re: how to write formula when doing query

Hi @Troyxx 

Profs? 🤔 I think I like that 😊

Paste this in a blank query tosee the steps and adapt it to your table:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lci5DQAgCADAXagt+HUXwv5rSIDqkosAYYE8pWCr6CPNK+/zbQ11JFrfyNY60lqfHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}}),
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Index", type text}}, "en-US"), "Index", Splitter.SplitTextByPositions({0, 2}, true), {"Month", "Day"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Month", Int64.Type}, {"Day", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Month] > (Date.Month(DateTime.LocalNow()) - 3) and [Month] <= Date.Month(DateTime.LocalNow()))),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Latest in month", each List.Max(Table.SelectRows(#"Filtered Rows", (inner)=> inner[Month] = [Month])[Day])),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Latest in month] = [Day]))
in
    #"Filtered Rows1"

 

 

It could be more compact but I've separated each of the steps  on purpose for the sake of clarity.

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

Highlighted
Solution Supplier
Solution Supplier

Re: how to write formula when doing query

@AlB  you've missed Feb/Jan (2, 1) and the case they have data from last year:
If it's february the code will get only Jan & Feb's binaries, and if it's June but they have binaries from last December the code will get those as well.

@TroyxxThis should work as long as you don't have more than a year's data in the same folder. If you do, you need to add a Year dimension in your index.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WystX0lEyNjJWitWBcYwNEBwTAzMkjiGSMhMjiLLK1GIwzxwhZWpggsQxNETmWCBpMjUyReKZGVggFBoaIbvC0AjkwFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Index = _t]),
    PreviousStep = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}}),
    Index2 = Table.AddColumn(PreviousStep, "Index2", each ([Index] - Number.Mod([Index], 100))/100, type number),
    Last3m = let d = DateTime.LocalNow(), l = {Date.Month(d), Date.Month(Date.AddMonths(d, -1)), Date.Month(Date.AddMonths(d, -2))} in Table.SelectRows(Index2, each [Index2] = l{0} or [Index2] = l{1} or [Index2] = l{2}),
    MaxDate = Table.SelectColumns(Table.Group(Last3m, {"Index2"}, {{"Max", each List.Max([Index]), type number}}),{"Max"}),
    RemoveOther = Table.RemoveColumns(Table.NestedJoin(PreviousStep, {"Index"}, MaxDate, {"Max"}, "d", JoinKind.Inner), {"d"}),
    AddDate = let d = DateTime.LocalNow() in Table.AddColumn(RemoveOther, "Month", each if [Index]-(Date.Month(d)+1)*100 > 0 then #date(Date.Year(d)-1, ([Index] - Number.Mod([Index], 100))/100, 1) else #date(Date.Year(d), ([Index] - Number.Mod([Index], 100))/100, 1), type date)
in
    AddDate

 


Or change #"Filtered Rows" in AIB's code to Last3m.




Feel free to connect with me:
LinkedIn

View solution in original post

5 REPLIES 5
Highlighted
Super User III
Super User III

Re: how to write formula when doing query

Hi @Troyxx 

Profs? 🤔 I think I like that 😊

Paste this in a blank query tosee the steps and adapt it to your table:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lci5DQAgCADAXagt+HUXwv5rSIDqkosAYYE8pWCr6CPNK+/zbQ11JFrfyNY60lqfHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}}),
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Index", type text}}, "en-US"), "Index", Splitter.SplitTextByPositions({0, 2}, true), {"Month", "Day"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Month", Int64.Type}, {"Day", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Month] > (Date.Month(DateTime.LocalNow()) - 3) and [Month] <= Date.Month(DateTime.LocalNow()))),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Latest in month", each List.Max(Table.SelectRows(#"Filtered Rows", (inner)=> inner[Month] = [Month])[Day])),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Latest in month] = [Day]))
in
    #"Filtered Rows1"

 

 

It could be more compact but I've separated each of the steps  on purpose for the sake of clarity.

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

Highlighted
Solution Supplier
Solution Supplier

Re: how to write formula when doing query

@AlB  you've missed Feb/Jan (2, 1) and the case they have data from last year:
If it's february the code will get only Jan & Feb's binaries, and if it's June but they have binaries from last December the code will get those as well.

@TroyxxThis should work as long as you don't have more than a year's data in the same folder. If you do, you need to add a Year dimension in your index.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WystX0lEyNjJWitWBcYwNEBwTAzMkjiGSMhMjiLLK1GIwzxwhZWpggsQxNETmWCBpMjUyReKZGVggFBoaIbvC0AjkwFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Index = _t]),
    PreviousStep = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}}),
    Index2 = Table.AddColumn(PreviousStep, "Index2", each ([Index] - Number.Mod([Index], 100))/100, type number),
    Last3m = let d = DateTime.LocalNow(), l = {Date.Month(d), Date.Month(Date.AddMonths(d, -1)), Date.Month(Date.AddMonths(d, -2))} in Table.SelectRows(Index2, each [Index2] = l{0} or [Index2] = l{1} or [Index2] = l{2}),
    MaxDate = Table.SelectColumns(Table.Group(Last3m, {"Index2"}, {{"Max", each List.Max([Index]), type number}}),{"Max"}),
    RemoveOther = Table.RemoveColumns(Table.NestedJoin(PreviousStep, {"Index"}, MaxDate, {"Max"}, "d", JoinKind.Inner), {"d"}),
    AddDate = let d = DateTime.LocalNow() in Table.AddColumn(RemoveOther, "Month", each if [Index]-(Date.Month(d)+1)*100 > 0 then #date(Date.Year(d)-1, ([Index] - Number.Mod([Index], 100))/100, 1) else #date(Date.Year(d), ([Index] - Number.Mod([Index], 100))/100, 1), type date)
in
    AddDate

 


Or change #"Filtered Rows" in AIB's code to Last3m.




Feel free to connect with me:
LinkedIn

View solution in original post

Highlighted
Helper I
Helper I

Re: how to write formula when doing query

Can I accept both two as solution....

 

Both Profs are really helpful.  @AlB @Smauro 

I am just wondering usually for Profs, how do you recreate the scenarios, did you just create the table in power bi and figure out the solution, or use M language code in the advanced editor, and for the second prof @Smauro thanks to reminding of adding the last 3m, that is important.

One more question is that how do you add yes and no for the table at first place, what is the purpose of that @Smauro 

really appreciate your help Profs.

Can I give accept the solution to Smauro @AlB  since you are Super user 3 already 😄

Highlighted
Super User III
Super User III

Re: how to write formula when doing query

Hi @Troyxx 

1. Yes, you can accept both as solutions (just click on "Accept as solution") or only one, whatever you prefer.

2. Yes, you usually create a table through the "Enter data" option. That creates a query in PQ with the two first steps in the code above (basically the data entered compressed) and then we can add the following steps up to the solution. The additional steps can be entered either through the GUI or directly on the advanced editor area. Sometimes you can start a step through the GUI and then modify the M code in the advanced editor if necessary, to adapt it to the requirements.

 

As a final note, it is important that you show your input table in text-tabular format instead of on a screen capture. If you paste it in text format, we can copy it and paste it directly as the start of the query through "Enter data", and then work from there. That makes it very quick and convenient. With a screen capture, we might have to enter the data manually, which can be time-consuming and might lower the chances of your question being answered quickly. So do not forget that.

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

    

Highlighted
Helper I
Helper I

Re: how to write formula when doing query

Hi Prof

 

Understood, thank you very much@AlB 😄

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors