Add a row when combining excel sheets in Power Query

%3CLINGO-SUB%20id%3D%22lingo-sub-2731278%22%20slang%3D%22en-US%22%3EAdd%20a%20row%20when%20combining%20excel%20sheets%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2731278%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3ERight%20now%20I%20have%20week1%20excel%20sheet%20and%20have%20setup%20the%20data%20as%20I%20want%20to%20get%20a%20table%20that%20I'm%20happy%20with.%20Now%20when%20week2%20comes%20in%2C%20is%20there%20a%20way%20to%20add%20a%20new%20indication%20that%20%22week%202%20table%22%20starts%20from%20this%20point.%20So%20that%20you%20I%20have%20all%20the%20information%20from%20week%201%20in%20a%20table%2C%20then%20when%20I%20add%20week2%20in%20the%20folder%20where%20the%20documents%20sync%2C%20add%20a%20space%20and%20have%20week2%20in%20the%20same%20sheet%20but%20a%20row%20lower%20than%20week1.%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2731278%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2731308%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20a%20row%20when%20combining%20excel%20sheets%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2731308%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1131204%22%20target%3D%22_blank%22%3E%40scaffoold2021%3C%2FA%3E%26nbsp%3BThe%20whole%20idea%20of%20PQ%20is%20to%20mash-up%20potentially%20unstructured%20and%2For%20%22dirty%22%20data%20into%20one%20structured%20table%20with%20clean%20data%2C%20or%20a%20sub-set%20thereof.%20Wanting%20a%20table%20with%20an%20empty%20row%20to%20identify%20groups%20of%20data%20that%20belong%20together%2C%20is%20definitely%20%3CSTRONG%3Enot%3C%2FSTRONG%3E%26nbsp%3Bsomething%20that%20complies%20with%20this%20idea.%20When%20you%20connect%20to%20Files%20from%20a%20folder%20with%20PQ%20you%20can%20include%20a%20column%20that%20holds%20the%20origin%20(i.e.%20source)%20for%20the%20data.%20So%2C%20if%20you%20call%20the%20files%20%22Week%201.xlsx%22%2C%20%22Week%202.xlsx%22%20etc.%2C%20these%20files%20names%20can%20be%20reflected%20in%20a%20separate%20column%20on%20each%20row%20in%20the%20PQ%20output.%20This%20will%20enable%20you%20to%20analyse%20the%20data%20further%20for%20e.g.%20only%20weeks%201%20through%204.%20But%2C%20if%20your%20weekly%20files%20already%20have%20a%20date%20stamp%20on%20every%20row%2C%20you%20don't%20need%20this.%20PQ%20has%20built-in%20date%20and%20time%20intelligence%20that%20enables%20you%20to%20do%20all%20sorts%20of%20date%2Ftime%20related%20analysis.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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

@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.