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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
AlB
Super User
Super User

Hi @Anonymous 

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

@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.

@AnonymousThis 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
Anonymous
Not applicable

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 😄

Hi @Anonymous 

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

    

Anonymous
Not applicable

Hi Prof

 

Understood, thank you very much@AlB 😄

AlB
Super User
Super User

Hi @Anonymous 

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

@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.

@AnonymousThis 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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors