SOLVED

Power Query - Is it possible to append tables with overlapping data

%3CLINGO-SUB%20id%3D%22lingo-sub-2879490%22%20slang%3D%22en-US%22%3EPower%20Query%20-%20Is%20it%20possible%20to%20append%20tables%20with%20overlapping%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2879490%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20new%20to%20this%20community%20and%20was%20hoping%20to%20get%20some%20guidance%20around%20tackling%20an%20situation%20I%20am%20facing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20receive%20a%20data%20in%20a%20table%20format%20for%20last%20100days%20every%20week.%20I%20would%20like%20to%20append%20the%20tables%20and%20create%20one%20table%20from%20all%20the%20files%20however%2C%20I%20cannot%20think%20of%20a%20way%20to%20active%20this%20using%20power%20query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20rolling%2014day%20data%20table%20below%20as%20an%20example.%3C%2FP%3E%3CP%3ETable%201%3C%2FP%3E%3CTABLE%20width%3D%22153%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2277%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%2276%22%3Edata%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F01%2F2021%3C%2FTD%3E%3CTD%3E11%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%2F01%2F2021%3C%2FTD%3E%3CTD%3E86%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%2F01%2F2021%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%2F01%2F2021%3C%2FTD%3E%3CTD%3E39%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%2F01%2F2021%3C%2FTD%3E%3CTD%3E21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%2F01%2F2021%3C%2FTD%3E%3CTD%3E35%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%2F01%2F2021%3C%2FTD%3E%3CTD%3E46%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8%2F01%2F2021%3C%2FTD%3E%3CTD%3E58%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F01%2F2021%3C%2FTD%3E%3CTD%3E73%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10%2F01%2F2021%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E11%2F01%2F2021%3C%2FTD%3E%3CTD%3E71%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12%2F01%2F2021%3C%2FTD%3E%3CTD%3E35%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E13%2F01%2F2021%3C%2FTD%3E%3CTD%3E81%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E14%2F01%2F2021%3C%2FTD%3E%3CTD%3E44%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EThe%20next%20week%20I%20would%20receive%20a%20table%20like%20below%3C%2FP%3E%3CTABLE%20width%3D%22153%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2277%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%2276%22%3Edata%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%2F01%2F2021%3C%2FTD%3E%3CTD%3E94%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8%2F01%2F2021%3C%2FTD%3E%3CTD%3E23%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F01%2F2021%3C%2FTD%3E%3CTD%3E47%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10%2F01%2F2021%3C%2FTD%3E%3CTD%3E70%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E11%2F01%2F2021%3C%2FTD%3E%3CTD%3E97%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12%2F01%2F2021%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E13%2F01%2F2021%3C%2FTD%3E%3CTD%3E77%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E14%2F01%2F2021%3C%2FTD%3E%3CTD%3E58%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E15%2F01%2F2021%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E16%2F01%2F2021%3C%2FTD%3E%3CTD%3E43%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E17%2F01%2F2021%3C%2FTD%3E%3CTD%3E41%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E18%2F01%2F2021%3C%2FTD%3E%3CTD%3E71%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E19%2F01%2F2021%3C%2FTD%3E%3CTD%3E96%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E20%2F01%2F2021%3C%2FTD%3E%3CTD%3E57%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E21%2F01%2F2021%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20would%20like%20to%20append%20the%20two%20tables%20and%20create%20a%20table%20which%20contains%20data%20for%201-21%20Jan%2C%20but%20avoid%20duplicating%2007-14%20Jan%20data%2C%20is%20this%20possible%20using%20power%20query%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2879490%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2879804%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Is%20it%20possible%20to%20append%20tables%20with%20overlapping%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2879804%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1194672%22%20target%3D%22_blank%22%3E%40lucioxiii%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3Ebut%20avoid%20duplicating%2007-14%20Jan%20data%2C%20is%20this%20possible%20using%20power%20query%3F%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FBLOCKQUOTE%3E%3CP%3EThere%20is%20no%20duplicate%20data%20in%20that%20period%20-%20apart%20from%20the%20dates%20themself.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2880508%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Is%20it%20possible%20to%20append%20tables%20with%20overlapping%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2880508%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20pointing%20that%20out.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20updated%20the%20data%20in%20the%20tables.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETable%201%3C%2FP%3E%3CTABLE%20width%3D%22171%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22107%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%2264%22%3Edata%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%2F01%2F2021%3C%2FTD%3E%3CTD%3E11%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%2F01%2F2021%3C%2FTD%3E%3CTD%3E86%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%2F01%2F2021%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%2F01%2F2021%3C%2FTD%3E%3CTD%3E39%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%2F01%2F2021%3C%2FTD%3E%3CTD%3E21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%2F01%2F2021%3C%2FTD%3E%3CTD%3E35%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%2F01%2F2021%3C%2FTD%3E%3CTD%3E94%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8%2F01%2F2021%3C%2FTD%3E%3CTD%3E23%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F01%2F2021%3C%2FTD%3E%3CTD%3E47%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10%2F01%2F2021%3C%2FTD%3E%3CTD%3E70%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E11%2F01%2F2021%3C%2FTD%3E%3CTD%3E97%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12%2F01%2F2021%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E13%2F01%2F2021%3C%2FTD%3E%3CTD%3E77%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E14%2F01%2F2021%3C%2FTD%3E%3CTD%3E%3CP%3E58%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETable%202%3C%2FP%3E%3CTABLE%20width%3D%22141%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2277%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%2264%22%3Edata%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%2F01%2F2021%3C%2FTD%3E%3CTD%3E94%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8%2F01%2F2021%3C%2FTD%3E%3CTD%3E23%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F01%2F2021%3C%2FTD%3E%3CTD%3E47%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10%2F01%2F2021%3C%2FTD%3E%3CTD%3E70%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E11%2F01%2F2021%3C%2FTD%3E%3CTD%3E97%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12%2F01%2F2021%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E13%2F01%2F2021%3C%2FTD%3E%3CTD%3E77%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E14%2F01%2F2021%3C%2FTD%3E%3CTD%3E58%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E15%2F01%2F2021%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E16%2F01%2F2021%3C%2FTD%3E%3CTD%3E43%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E17%2F01%2F2021%3C%2FTD%3E%3CTD%3E41%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E18%2F01%2F2021%3C%2FTD%3E%3CTD%3E71%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E19%2F01%2F2021%3C%2FTD%3E%3CTD%3E96%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E20%2F01%2F2021%3C%2FTD%3E%3CTD%3E57%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E21%2F01%2F2021%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

 

I am new to this community and was hoping to get some guidance around tackling an situation I am facing.

 

I receive a data in a table format for last 100days every week. I would like to append the tables and create one table from all the files however, I cannot think of a way to active this using power query.

 

I have created rolling 14day data table below as an example.

Table 1

Datedata
1/01/202111
2/01/202186
3/01/202120
4/01/202139
5/01/202121
6/01/202135
7/01/202146
8/01/202158
9/01/202173
10/01/20215
11/01/202171
12/01/202135
13/01/202181
14/01/202144

The next week I would receive a table like below

Datedata
7/01/202194
8/01/202123
9/01/202147
10/01/202170
11/01/202197
12/01/202115
13/01/202177
14/01/202158
15/01/202110
16/01/202143
17/01/202141
18/01/202171
19/01/202196
20/01/202157
21/01/202120

 

So I would like to append the two tables and create a table which contains data for 1-21 Jan, but avoid duplicating 07-14 Jan data, is this possible using power query?

 

Thanks in advance.

 

 

 

5 Replies

@lucioxiii 

 

but avoid duplicating 07-14 Jan data, is this possible using power query?

 

There is no duplicate data in that period - apart from the dates themself.

 

@Detlef Lewin 

 

Thanks for pointing that out. 

 

I have updated the data in the tables.

 

Table 1

Datedata
1/01/202111
2/01/202186
3/01/202120
4/01/202139
5/01/202121
6/01/202135
7/01/202194
8/01/202123
9/01/202147
10/01/202170
11/01/202197
12/01/202115
13/01/202177
14/01/2021

58

 

Table 2

Datedata
7/01/202194
8/01/202123
9/01/202147
10/01/202170
11/01/202197
12/01/202115
13/01/202177
14/01/202158
15/01/202110
16/01/202143
17/01/202141
18/01/202171
19/01/202196
20/01/202157
21/01/202120
best response confirmed by lucioxiii (Occasional Contributor)
Solution

@lucioxiii 

After you append one to another, in Power Query editor select both columns 

Home -> Remove Rows -> Remove Duplicates