SOLVED

Add a row when combining excel sheets in Power Query

Brass Contributor

Hi all,

Right now I have week1 excel sheet and have setup the data as I want to get a table that I'm happy with. Now when week2 comes in, is there a way to add a new indication that "week 2 table" starts from this point. So that you I have all the information from week 1 in a table, then when I add week2 in the folder where the documents sync, add a space and have week2 in the same sheet but a row lower than week1.

Thanks!

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@scaffnull The whole idea of PQ is to mash-up potentially unstructured and/or "dirty" data into one structured table with clean data, or a sub-set thereof. Wanting a table with an empty row to identify groups of data that belong together, is definitely not something that complies with this idea. When you connect to Files from a folder with PQ you can include a column that holds the origin (i.e. source) for the data. So, if you call the files "Week 1.xlsx", "Week 2.xlsx" etc., these files names can be reflected in a separate column on each row in the PQ output. This will enable you to analyse the data further for e.g. only weeks 1 through 4. But, if your weekly files already have a date stamp on every row, you don't need this. PQ has built-in date and time intelligence that enables you to do all sorts of date/time related analysis.

@Riny_van_Eekelen Perfect, and makes sense! Thank you for the help.

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@scaffnull The whole idea of PQ is to mash-up potentially unstructured and/or "dirty" data into one structured table with clean data, or a sub-set thereof. Wanting a table with an empty row to identify groups of data that belong together, is definitely not something that complies with this idea. When you connect to Files from a folder with PQ you can include a column that holds the origin (i.e. source) for the data. So, if you call the files "Week 1.xlsx", "Week 2.xlsx" etc., these files names can be reflected in a separate column on each row in the PQ output. This will enable you to analyse the data further for e.g. only weeks 1 through 4. But, if your weekly files already have a date stamp on every row, you don't need this. PQ has built-in date and time intelligence that enables you to do all sorts of date/time related analysis.

View solution in original post