Merging queries from different files

M

mkuzner

New member
Joined
Nov 29, 2019
Messages
15
Reaction score
0
Points
1
Excel Version(s)
365
Hello guys,

let me first describe my situation. I have 31 Excel files (pretty much the same, but still slightly different), where my colleagues input some data regarding our production/maintenance results (the tables are of course not in tabular format, which I am trying to achieve, rather they are pivoted by the months, budget...). Excel files have different sheets for different years and currently I am working on year 2020. MY path was to build 31 queries with copying the code, slighty modificating it, creating connections and in the end appending them to create one table to calculate and analyse (all in all around 8000 rows).

Several problems/challenges arose during the process:
- after partial query nr. 23, 24 Excel became EXTREMLY slow, creating last 7 queries in at least triple amount of time than the first 24
- in 2021 all initial files will be given a new sheet called 2021 for colleagues to insert new data and if I had to create another 31 queries, Excel simply won't handle it. I was thinking of creating a new base file (just copying the existing 2020) to modify queries to year 2021 (which by the current speed will take a day or so), but then I will have two files with appended queries for data in 2020 and data in 2021. How to than create a third file to query those two appended queries and put them together? Is there even a posibility to query two or several queries from different Excel files? And what happens in 2022, 2023...can't even imagine.

Just by reading post, I am 120% sure there must be an easier and more efficient solution. :) I will be delighted if someone has any ideas.

Thank you for your help in advance.

Regards, Marko

P.S. I am posting the query code from one of 31 queries so you'll see what I am dealing with.
Code: 
let
    Vir = Excel.Workbook(File.Contents("d:\downloads\test.xlsx"), null, true),
    #"ACT 2020_Sheet" = Vir{[Item="ACT 2020",Kind="Sheet"]}[Data],
    #"Povišane glave" = Table.PromoteHeaders(#"ACT 2020_Sheet", [PromoteAllScalars=true]),
    #"Spremenjena vrsta" = Table.TransformColumnTypes(#"Povišane glave",{{"KPIs Report", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type text}, {"Column7", type any}, {"Column8", type any}, {"Column9", type number}, {"Column10", type any}, {"Column11", type number}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type text}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}}),
    #"Odstranjene zgornje vrstice" = Table.Skip(#"Spremenjena vrsta",5),
    #"Povišane glave1" = Table.PromoteHeaders(#"Odstranjene zgornje vrstice", [PromoteAllScalars=true]),
    #"Odstranjeni drugi stolpci1" = Table.SelectColumns(#"Povišane glave1",{"Column1", "Description", "Unit", "BUD 2020", "Goal 2020", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}),
    #"Spremenjena vrsta1" = Table.TransformColumnTypes(#"Odstranjeni drugi stolpci1",{{"Column1", type text}, {"Description", type text}, {"Unit", type text}, {"BUD 2020", type number}, {"Goal 2020", type text}, {"January", type number}, {"February", type number}, {"March", type number}, {"April", type number}, {"May", type number}, {"June", type number}, {"July", type number}, {"August", type number}, {"September", type number}, {"October", type any}, {"November", type any}, {"December", type any}}),
    #"Prazne vrstice so bile odstranjene" = Table.SelectRows(#"Spremenjena vrsta1", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Izpolnjeno navzdol" = Table.FillDown(#"Prazne vrstice so bile odstranjene",{"Column1", "Description"}),
    #"Filtrirane vrstice" = Table.SelectRows(#"Izpolnjeno navzdol", each ([Description] <> "Average batch size (final product)" and [Description] <> "Productivity" and [Description] <> "Sickness hours " and [Description] <> "Utilization budget" and [Description] <> "Utilization theory")),
    #"Zamenjana vrednost" = Table.ReplaceValue(#"Filtrirane vrstice",null,0,Replacer.ReplaceValue,{"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}),
    #"Zamenjane napake" = Table.ReplaceErrorValues(#"Zamenjana vrednost", {{"BUD 2020", 0}}),
    #"Odvrteni drugi stolpci" = Table.UnpivotOtherColumns(#"Zamenjane napake", {"Column1", "Description", "Unit", "BUD 2020", "Goal 2020"}, "Atribut", "Vrednost"),
    #"Deljeni stolpec" = Table.TransformColumns(#"Odvrteni drugi stolpci", {{"BUD 2020", each _ / 12, type number}}),
    #"Spremenjena vrsta2" = Table.TransformColumnTypes(#"Deljeni stolpec",{{"Goal 2020", Int64.Type}}),
    #"Deljeni stolpec1" = Table.TransformColumns(#"Spremenjena vrsta2", {{"Goal 2020", each _ / 12, type number}}),
    #"Preimenovani stolpci" = Table.RenameColumns(#"Deljeni stolpec1",{{"Vrednost", "#"}}),
    #"Spremenjena vrsta3" = Table.TransformColumnTypes(#"Preimenovani stolpci",{{"#", Int64.Type}}),
    #"Dodano po meri" = Table.AddColumn(#"Spremenjena vrsta3", "Production plant", each "XXX name"),
    #"Znova razvrščeni stolpci" = Table.ReorderColumns(#"Dodano po meri",{"Production plant", "Column1", "Description", "Unit", "BUD 2020", "Goal 2020", "Atribut", "#"}),
    #"Dodano po meri1" = Table.AddColumn(#"Znova razvrščeni stolpci", "Production unit", each "YYY"),
    #"Znova razvrščeni stolpci1" = Table.ReorderColumns(#"Dodano po meri1",{"Production plant", "Production unit", "Column1", "Description", "Unit", "BUD 2020", "Goal 2020", "Atribut", "#"}),
    #"Dodano po meri2" = Table.AddColumn(#"Znova razvrščeni stolpci1", "Group", each "ZZZ"),
    #"Znova razvrščeni stolpci2" = Table.ReorderColumns(#"Dodano po meri2",{"Production plant", "Production unit", "Group", "Column1", "Description", "Unit", "BUD 2020", "Goal 2020", "Atribut", "#"}),
    #"Preimenovani stolpci1" = Table.RenameColumns(#"Znova razvrščeni stolpci2",{{"Column1", "Category"}}),
    #"Dodano po meri3" = Table.AddColumn(#"Preimenovani stolpci1", "Year", each "2020"),
    #"Vstavljen spojeni stolpec" = Table.AddColumn(#"Dodano po meri3", "Spojeno", each Text.Combine({"1.", [Atribut], ".", [Year]}), type text),
    #"Spremenjena vrsta4" = Table.TransformColumnTypes(#"Vstavljen spojeni stolpec",{{"Spojeno", type date}}),
    #"Vstavljeno četrtletje" = Table.AddColumn(#"Spremenjena vrsta4", "Četrtletje", each Date.QuarterOfYear([Spojeno]), Int64.Type),
    #"Spremenjena vrsta5" = Table.TransformColumnTypes(#"Vstavljeno četrtletje",{{"Četrtletje", type text}}),
    #"Zamenjana vrednost1" = Table.ReplaceValue(#"Spremenjena vrsta5","1","Q1",Replacer.ReplaceText,{"Četrtletje"}),
    #"Zamenjana vrednost2" = Table.ReplaceValue(#"Zamenjana vrednost1","2","Q2",Replacer.ReplaceText,{"Četrtletje"}),
    #"Zamenjana vrednost3" = Table.ReplaceValue(#"Zamenjana vrednost2","3","Q3",Replacer.ReplaceText,{"Četrtletje"}),
    #"Zamenjana vrednost4" = Table.ReplaceValue(#"Zamenjana vrednost3","4","Q4",Replacer.ReplaceText,{"Četrtletje"}),
    #"Preimenovani stolpci2" = Table.RenameColumns(#"Zamenjana vrednost4",{{"Četrtletje", "Q"}}),
    #"Vstavljen spojeni stolpec1" = Table.AddColumn(#"Preimenovani stolpci2", "Spojeno.1", each Text.Combine({[Q], " ", [Year]}), type text),
    #"Preimenovani stolpci3" = Table.RenameColumns(#"Vstavljen spojeni stolpec1",{{"Spojeno.1", "Q YEAR"}})
in
    #"Preimenovani stolpci3"
 
Last edited:
Did you look at the description? I think that if you change your queries according to the description, you will need much less queries and the maintenance effort will be much lower.
 
You must log in or register to reply here.
Back
Top