I'm trying to import 4 different excel documents with Power Query in Excel. So I'm using the formula at the end of the post. To give you context I have a survey for 8 cities so I'm trying to create multiple graphs and charts, so the survey is applied to every city and obviusly in each city I have different ammounts of data(Rows depending on the number of people who participate) so to avoid creating multiple documents and repiting creating graphs and save time I want to create a file using power query to be able and just copy and pasting to replace the data from one city for the second and then the third.
Example of data: File with data city 1 So here is an example with the data from two cities files with data city 2, when I tried to use the formula the power query import the data to excel but when I replace that file with the one from the city 2 appears an error.
My desired output is have the data imported in a sheet in excel that doesn't mind which of the city files is in the folder but that update every time that I replace the excel file from other city.
let
Source = Excel.Workbook(File.Contents("C:\Users\user\OneDrive\Documents\results-city.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}})
in
#"Changed Type"