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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RebekkaLuna
Frequent Visitor

Top 5 upcoming dates

Hi. I have a dataset that consists of 12 columns with all sorts of dates 1 column with project id (not inc. in pic). 

I would like to look up the five date column that contains the date closest to the current, today. Not like today, but like any present day.

 

In order for that  be useful I would need to combine those 5 dates with the project id that the dates belongs to.

 

How can I do that?

 

 

 

3 REPLIES 3
RebekkaLuna
Frequent Visitor

@edhans I did scribed my dataset very well because it is so simple. 

I do not want to enter a fil into my work pc that I do not know that is.

 

Is there another solution?

No. I am unable to read minds. Happy to help, but you must give info. I have a dataset on my PC right now. It is very simple. Can you describe it for me? No, of course not. It is simple for me because I can see it and understand it. It is not simple for you because you no idea what it looks like.

It is the same with me. What is simple for you is undescribable for me. You cannot assume I can look over your shoulder and see what you are talking about.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

You didn't really provide enough info. You gave no data, and you refrenced an image that doesn't exist. But you can use this code to keep only the most recent 5 dates for every project ID.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZRJjoQwDEXvwrqkJCZkOAti0fNw/wM0JUj5mXZLvYPg2M/f/qzr9DTdppSCzEGizOdLHS8tpPFY+dgREPVY8iOLBM0YQyq48Mi+Z+kse97Ybuv0fByljCRClo6qiyYXVpXu97EAAK2KkBhXi3K9XFH2a81lbJaxsFEBQHFU3RNVsjRe5oeuZK9HzfwPoXUaPSBeImPaHx0mgZIR1aDqOL9zvZ35kGJhOxnpxA2KgdszI54lKRiFxOCxX++OsgareJ1dELV98lbDmKlpVl6BbapyfVy222BFoDSsrjFbwriaNSHiOQ6N75f/QFKwz1+CRTM7cbNX68HoUlZDKe6m0EE9gOvr0AKbAInMeKm+jtHaJIknRbVQxmP09Sh35/o+YmeuiPE0CYS6cHn5IRVPMKGDjU9ZefS4bT8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, Date1 = _t, Date2 = _t, Date3 = _t, Date4 = _t, Date5 = _t, Date6 = _t, Date7 = _t, Date8 = _t, Date9 = _t, Date10 = _t, Date11 = _t, Date12 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date2", type date}, {"Date3", type date}, {"Date4", type date}, {"Date5", type date}, {"Date6", type date}, {"Date7", type date}, {"Date8", type date}, {"Date9", type date}, {"Date10", type date}, {"Date11", type date}, {"Date12", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project ID"}, "Attribute", "Value"),
    #"Grouped Rows" = 
        Table.Group(
            #"Unpivoted Other Columns", 
            {"Project ID"}, 
            {
                {   
                    "AllRows", 
                    each 
                        Table.FirstN(
                            Table.Sort(_,  {{"Value", Order.Descending}}),
                            5), 
                            type table [Project ID=nullable text, Attribute=text, Value=date]}
            }
        ),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Attribute", "Value"}, {"Attribute", "Value"})
in
    #"Expanded AllRows"
    //= Table.Sort(#"Unpivoted Other Columns",{{"Value", Order.Descending}})

 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors