Power Query - Dynamic Range Detection

Learn how to discover the start and end of a data range within a file where the start and/or end of the data are not predictable.

File Download Link:

00:12 Overview of the Problem
00:46 File Download Instructions
00:58 Solving the Problem
01:18 Examining the Static (i.e., Common) Solution
02:11 Creating a Dynamic Discovery Solution (Finding the Start of the Data)
03:15 Discovering the End of the Data
04:32 Testing the Dynamic Solution
05:26 Overview of the M Code Solution

[benndii]

I only use Power Query for work, but I enjoy watching your videos in my free time. Could be because you are such a good story teller. 🧡

[IrfanChanna]

I used to find Power Query quite challenging, but after watching your videos, it now feels so much easier.

[hanhtayaung]

This is another practically applicable tutorial. Thank you so much.

[SaniGarba]

I’ve viewed several tutorials regarding dynamically identifying header rows. I can tell you this, to me, is the best solution of its category. To make it complete, kindly create a function that can be applied to multiple files in a single folder at one go.

Well done.

[kkravch]

Great tutorial as usual. Appreciate your ability to explain complex concepts in simple terms. Happy New Year!

[hichamhadj]

Yet another great video. You sir are a genius, thanks you for making learning excel this fun

[kebincui]

Great video as always. Thanks for sharing

[joelabacherli]

Table.Skip is much better than the way I used to do it with List.Positionof. Pardon the pun but it skips a step which is always good. Excellent content. I really like the format of your vids.

[truewiking]

This is brilliant, Thank you very much

[IvanCortinas_ES]

Excellent dynamic solution!! Thank you very much for sharing!!

[baskis]

Very useful, thanks for sharing. Happy New Year!

[paintsey]

Great vid. I would just filter on say the date column to <> blank to keep my query dynamic.

[arunsubramanian]

You're the best! Keep up the good work man!!

[celestebenitez]

Amazing! Thanks so much!!!
Happy New Year to you and yours.

[michaelt]

Another great video. Could you continue this in a way to combine all .csv files in the folder? So both in this particular instance.

[boissierepascal]

Another way to select the good range of values in one go that might please you.

// Select range of values
Find_Range = Table.Range(Source, List.PositionOf(Source[Column1], "Sales Representative"), List.PositionOf(Source[Column1], "Total Sales")-List.PositionOf(Source[Column1], "Sales Representative")),

[JM-mbtf]

Hi Brian. Thank you very much for another educative and great video.
Question: PQ / M-code contains like 800 different functions. Where should I start ?? I know where I want to go and what the result of the import should be, but I have not the slightest clue which function I should use.
I have studied the m code like you do in the videos - try something "automatically" and then tweak the code provided by the program but, well, I let´s say that my end results are not as good as yours.
As always - thank you for your videos and dedication. I, and many more are very greatful for the time and work you spend on this.

[attakbeer]

Great video. Thanks for sharing. Please can these methods also be applied to other source files like PDF

[Blueboy]

Can you do a version of this if the information in a column changes position? eg. column 1 has sales next time it appears in column 2. PDF credit card statements do this.