Clean up messy data (multiple Excel Files in folder)

Hi All
Not sure if everyone has seen this. If so ignore
If you go into Power Query create a

  • Blank Query

  • Type in. =#shared

You will see a list of automated tasks Power Query can do, regardless if the data source is excel, sub folders you have, databases. etc. Me personally this list of Power Query fuctions has seriously been really useful! So many more ETL tasks you can do, check out the Text. (functions)

Thought i’ll share if anyone was interested



Totally agree, its a valuable thing to know the #shared Keyword

Power Query will suggest turning it into a Table on the Ribbon, do so because this will allow you to filter (or search) functions like: Date, Text or week and so on

And if you click in the white space of the Value column it’ll show function documentation and examples:

Another option if you already know the function name but want to see the documentation and examples is to just type this function name in the formula bar without the parenthesis and hit enter, like below


I think the key thing I like about this is just how intuitive the #Shared is.
Especially if your coming from an Excel background.

Yes Text.Combine is amazing!
I use Text.Range, and Text.Find a lot, as sometimes when I’m automating multiple excel spreadsheets coming from a folder its nice to know at which row of the data, which file the data comes from.