Відео

How to select multiple files

Is there an advantage to creating the date table in power query rather than power pivot? Are there pro's and con's for one way over the other?

Exactly what I was looking for

I have an issue I’ve been trying to solve and I’m hoping you can provide a direction. I have a table of employees with a start date and an end date. Current employees don’t have an end date. I need to calculate the number of active employees per month for the last 3 years. I’ve tried to use a join the data to a date table and use sumx and collect as a measure to calculate it, but since there isn’t a date for each month that there been employed it’s only counting each employee the first month they’re employed. Any thoughts?

Great explanantion but your code didn't work for me. Had to write my own. Maybe that's because Microsoft changed what happens when you create a web query? In my case it used Web.BrowserContents for the source. Then it created an Html.Table. I also got a dialog box that asked me to either ignore the privacy levels for this document or make the security levels the same which is a huge improvement over the way you showed. In case anyone is interested in my code: Made a named range for my URL and named it URL. Then typed this in the advanced editor: URLflex = Excel.CurrentWorkbook(){[Name="URL"]}[Content]{0}[Column1], Source= Web.BrowserContents(URLflex),

Very good Sir

HOW DID YOU CREATE THE Source CODE FOR THE TWO TABLES? DO YOU HAVE A FUNCTION YOU COULD SHARE? PLEASE???!!! Two comments. The start date must always be January 1 of the first year and the end date must always be December 31 of the last year. To get this I chose to use: StartDate = #date(Date.Year(List.Min(Transactions[Date])),1,1), EndDate = #date(Date.Year(List.Max(Transactions[Date])),12,31), Also, after the Fill Up operation, it's a LOT easier to filter null in the Year column than the Date column, especially for a calendar for more than one year. Still a valuable video!

Super!! Awsome!!

Great video. Nice to see someone doing videos targeted at the more advanced users. 😊

Number 9 for me. I have a particular long and complex additional column and this allows me to build and test the steps incrementally. Thank you.

Get an error EVERYTIME i try to enter this. I am using Office 365 Power Query. I have written it 4 times EXACTLY as you show and everytime I hit the DONE button I get this error "An error occurred in the ‘’ query. Expression.Error: The name 'Startdate' wasn't recognized. Make sure it's spelled correctly." Any idea, I give up on learninig if I get unexplained arrors all the time.

You are good, both in choosing the topic and make them easy to implement Thank you

We use 4,4 ,5 - your solution is good for a single year, not so good with a large date range - Had hoped it was going to use equivalent of Excels weeknum and type 21 - that's been stumping me a bit

DIDNT WORK, i followed your steps but after loading new data, my comments still gone to other rows....

Hi Mark. I really enjoyed the video and it will come in very useful. I see your dates in the calendar are in UK format. Can you help me an issue I appear to have suddenly noticed only this week? When I have dates in an Excel sheet or csv file in UK format they are now always pulling into Power Query in US format. I have tried using the Local option, but as soon as I change Type back to Date I then get an error for any date with the day greater than 12. This was working fine just last week. My Excel date setting is UK and so is my Windows settings. I can't think of any other reason this has started happening. Regards, Alan

Awesome Level of teach and special thanks

fantastic one! You could also have worked on Quarters for by CY and FY... that would of great use!!

Excelente video… 👍🏽

Fantastic video as always! Your PQ vids have been a lifesaver for my occasional large data tasks, from a crawl to swift delivery. ⚡️👍🏾

Thanks for the video ! I personally perfer the smaller m-code { Number.From( StartDate ) .. Number.From( EndDate ) } I hope the next video is about Time Table

Thank you for your video! It really helped a lot!👌

Very nice tricks, I used trick #9 it helped me a lot. Trick #1 with the line feed is great. But I have a problem, I have a list with those line feeds address. How do I get it back into its normal each column separated? Thank you for your very much for your tutorials.

Thanks, Mark!

Sign up for our free Insiders program and get the example file: exceloffthegrid.com/insiders-signup/ File Reference for this video: 0215 PQ Calendar Table.zip

Does it have to be myList? for the 2nd method. Cause its not working on my end

When I entered the formula for filtering with a list, I get a #VALUE! ERROR could anybody plese help?

Hello, how I can get the excel sheet please?

Watched Best video On FILTER functions ever.Thank you for Your Contribution to our Success

Very useful content, thanks a lot! It's just a static version w/o header from Mark's Option1, but it works with ranges, structured references and defined names as well. So only for lazy excel users 🤣🤣🤣 -------------------------------------------------------------------------------------------------------------------------- The function, based on the specified dates and values, prepares the source data for the X and Y axes of a Step Chart. Criteria: COUNTA(dates)=COUNTA(values) ARGS: (dates), (values) -------------------------------------------------------------------------------------------------------------------------- StepChartStaging=LAMBDA(dates, values, LET( criteria, COUNTA(dates) = COUNTA(values), dateoffset, DROP(dates, 1), valueoffset, DROP(values, -1), datecolumn, VSTACK(dateoffset, dates), valuecolumn, VSTACK(valueoffset, values), basetbl, HSTACK(datecolumn, valuecolumn), staging, IF( criteria, basetbl, "The row numbers of the (dates)<>(values) columns do not match!" ), staging)) --------------------------------------------------------------------------------------------------------------------------

when I put # behind a cell, It not work and return #REF error. Is there any solution. I use 365

Can you share the solution for Example 6 for older Excel version (without Lambada function). Thanks a lot.

It is not working anymore since Microsoft updated the link structure, anyone have solution?

Hello there, i have been using the power query for quite sometime , can you please tell how to extract if the sharepoint list have more than 5000lines in it? it throws error

Hello there, i have been using the power query for quite sometime , can you please tell how to extract if the sharepoint list have more than 5000lines in it? it throws error

Thanks, Thanks to Mr.Chandeep through whom we got to know you.

Absolutely amazing and in depth. I learned many things, I am happy to say!

great content on Microsoft excel

These VBA subs are amazing, extremely useful, thanks a lot!

Excellent presentation with more examples

Hi Mark, brilliant solutions, I'm impressed! Thanks a lot! 😇 Just one important thing to consider -> calculate event in case of auto calculate settings will kill our UNDO-CACHE every time when the sheet is calculated!

Great video and very helpful for different scenarios at work. Just one humble request, if you can please remove zap sound (or any sound!) from slide transitions. I listen to videos using ear plugs and while focusing on a topic and if suddenly zapping sound comes, it hurts. Thank you for your amazing work! ❤

Loved it!!

aim to simplify !, thank for this demonstration ;-)

Great explanation. Many times we find pivoted data structures. Power Query is always a great help. Thanks Mark.

Thank you for sharing this amazing video. Good job! I have a question. Could you also do an example with a filter function (dropdown list or slicer) and a scrollbar? Idea is to display in a dashboard only 10 rows and by scrolling down with the scrollbar you see the rest items. One more specific thing. The database has more column I want to show in a dashboard. So I only want to certain columns.

👏 amazing

Sir kindly make a video Sendkeys for onother program data entry please

I had a different approach to finding the average term in the unpivoted column. At first I thought it would be easier, but after I finished I'm not so sure. But without a lookup function, my gut tells me it might be quicker. =AVERAGE(CHOOSECOLS(UNIQUE(FILTER(Unpivoted[[Property]:[Term (Yrs)]],Unpivoted[Type]=J10)),3)) I also think unpivoted is better than pivoted, so it should have a score of 5 or 6.

Brilliant as always, thanks Mark

u can use consolidate icon in Data Ribbon