How to bulk edit tables from different sheets in power query?

[8BitLife]

How do I bulk remove the first 4 rows in each of these tables in power query and have it set up to automatically delete the first 4 rows of any future sheets I add the source folder:

https://imgur.com/a/3lWylAJ

 

[swift412]

tl;dr: Look for a Query named "Transform Sample File". Home > Reduce Rows > Remove Top Rows > Number of rows: 4. Save and refresh.

---

If you're using Get Data\From Folder, it will create a folder named "Transform File from (folder name"). In that folder is a Query named "Transform Sample File".

Any changes you make to this table (e.g. removing first 4 rows), will then apply to every table that uses this transformation.

If you are only removing rows, it should update your actual main query without any other changes required.

This main query is the Query with the same name as the folder you are importing, located under the default folder named "Other Queries".

One more step, which is not applicable in this particular situation, is that if you change the structure of the table (i.e. columns, not rows), the last step of your main query will fail because it is expecting exactly *n* columns with these *exact* names). So if you do change "Transform Sample File" and the original query breaks, that will be why.



Option B:

This method works any time you have
in a column, not just Get Data\From Folder.

Add Column (ribbon) > Custom Column

=Table.Skip[Custom.Data, 4]

This will create a new column, that duplicates the previous column, but applies the function Table.Skip to each individual table one at a time, row by row.

You can check that new column does what you want, then you can remove the original column.

 

[8BitLife]

tl;dr: Look for a Query named "Transform Sample File". Home > Reduce Rows > Remove Top Rows > Number of rows: 4. Save and refresh.

Thank for the detailed explanation. Really appreciate it!

Would you be able to assist with the attached file? Basically I have a dates column made from multiple workbooks. 90% of them have the date as year/month/day but one of them have the date as day/month/year. That messes everything up giving me the error. How can I fix the date format so that power query always knows to change it to year/month/day if it is the wrong way round?

 

[swift412]

Sure, no problem

1. Click the icon to the left of the column name that shows the column type
2. Choose Using Locale...
3. Change Data Type to Date
4. Change Locale to English (United Kingdom)

 

[8BitLife]

Sure, no problem

1. Click the icon to the left of the column name that shows the column type
2. Choose Using Locale...
3. Change Data Type to Date
4. Change Locale to English (United Kingdom)

Tried this one yesterday and didn't work. Instead trying to write a custom function using the Table.ReplaceErrorValues function to try and tell it to go through the Dates column, and if you find an error, just flip the text around. Dunno how to write the syntax for it though, do you maybe?

 

[swift412]

Try this one, it will try to parse it as the first locale, and if that results in an error, it'll try parsing with the second locale:

=try Date.From([Date],"en-ZA") otherwise Date.From([Date],"en-GB")

 

[8BitLife]

Try this one, it will try to parse it as the first locale, and if that results in an error, it'll try parsing with the second locale:

=try Date.From([Date],"en-ZA") otherwise Date.From([Date],"en-GB")

Thanks. Unfortunately still no success.

 

[8BitLife]

Even when I split the column by delimiter, it still keeps the data the same in error cells:


Why would that be?

 

[swift412]

Sorry man, I'm stumped too.

If I had to take a wild guess, its some mismatch between Windows' Regional Settings, Excel's own date locale settings, and PowerQuery.

It probably won't have the solution, but this thread was sort of tangentially related: https://mybroadband.co.za/forum/threads/excel-change-default-date-format.1055345/