Help on Appending Table

Power Query
1

Hello Team,

I have scenario where I need to append the files but with a twist where I have 3 files in folder but when I append I should get week 1 data in week 2 and week 1 & 2 in week 3 data and also this should dynamic if I get 4th week.

Basically I need the latest table to be combination of previous versions.

Any help on the same, I have attached small sample file and end result screenshot I wanted…

Week 1.xlsx (10.9 KB)
Week 2.xlsx (11.0 KB)
Week 3.xlsx (11.0 KB)

End result

image

2

Hi @Dharma,

Paste this code into a new blank query and replace the FolderLocation in the Source step with the correct path on your system.

let
    Source = Folder.Files( FolderLocation ),
    GetName = Table.RenameColumns( Table.TransformColumns(Source, {{"Name", each Text.BeforeDelimiter(_, "."), type text}}), {"Name", "Current week"}),
    GetData = Table.AddColumn(GetName, "Custom", each Table.SelectRows( Excel.Workbook([Content]), each [Kind] = "Table" )[Data]{0}),
    AddIndex = Table.AddIndexColumn(GetData, "Index", 1, 1, Int64.Type),
    NewTables = Table.AddColumn(AddIndex, "nTable", each Table.Combine( List.FirstN( AddIndex[Custom], [Index] )))[[Current week], [nTable]],
    ExpandNewTables = Table.ExpandTableColumn(NewTables, "nTable", {"Name", "Country", "Week"}, {"Name", "Country", "Week"})
in
    ExpandNewTables

.
It generates this output

image

I hope this is helpful

3

Hello @Dharma, just following up if the response above help you solve your inquiry?

If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

4

Hello @ @Dharma, just following up if the response from @Melissa help you solve your inquiry?

We’ve noticed that no response has been received from you since a few days ago. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

Hi @Dharma, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.