locked
Consolidate Data with power query RRS feed

  • Question

  • Hi All,

    If it is possible to combine data with power Query.

    For Example :- I have data in below format. Which I sent to around 30 peoples to update from column I to L.

    I received revert from all 30 persons. Now issue is that I need to fill all the information sent by them in master tracker same format. Could you please suggest if we can pickup the data from all those 30 different files and update the same in Master sheet in same references.

    Thanks in advance for your valuable comments

    Regards,

    Rajender

    Tuesday, July 14, 2015 4:31 PM

Answers

  • I'll get to the VBA in a minute. I want to make sure that Append Query won't work however.

    I want to make sure that the other methods will not work for you first. If you will always have all of the data that you want in your files, you could use either Folder or Append Query. If this is kind of a "period" thing where the files only contain the data for that period, but you want to preserve all of the data from the previous periods, probably won't work so well and have to go the VBA route or something else.

    What I did was create a folder and put two CSV files in it with the first one being:

    • Column1
    • one
    • two
    • three

    and the second one

    • Column1
    • four
    • five
    • six

    Created the following folder query:

    let
        Source = Folder.Files("C:\Temp\Append\import"),
        #"Combined Binaries" = Binary.Combine(Source[Content]),
        #"Imported CSV" = Csv.Document(#"Combined Binaries",null,null,null,1252),
        #"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}}),
        #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
        #"Removed Top Rows" = Table.Skip(#"Removed Duplicates",1)
    in
        #"Removed Top Rows"

    Ran this and got six rows. Created a third CSV as follows:

    • Column1
    • seven
    • eight
    • nine

    Ran the query again and got 9 rows. So, if you keep all of your files for all of your periods or if the data supplied has all of the history in them, this method should work as long as they are all in the same format.

    The Append Query works a little different and probably isn't as flexible as the Folder method in terms of adding file but has the flexibility to support different formats in the files because you can make them the same format using m code so that when you append them together they are in the same format.

    For Append, here are the queries:

    person1

    let
        Source = Csv.Document(File.Contents("C:\Temp\Append\import\person1.csv"),[Delimiter=",",Encoding=1252]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type")
    in
        #"Promoted Headers"

    person2

    let
        Source = Csv.Document(File.Contents("C:\Temp\Append\import\person2.csv"),[Delimiter=",",Encoding=1252]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type")
    in
        #"Promoted Headers"

    Append1

    let
        Source = Table.Combine({person1,person2})
    in
        Source

    To add "person3", create a new query person3

    let
        Source = Csv.Document(File.Contents("C:\Temp\Append\import\person3.csv"),[Delimiter=",",Encoding=1252]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type")
    in
        #"Promoted Headers"

    And then modify Append query to the following:

    let
        Source = Table.Combine({person1,person2,person3})
    in
        Source

    If neither of these are going to work in your specific circumstance I'll take a look at what can be done in VBA code.

    Thursday, July 23, 2015 5:26 PM
  • I'd encourage you to take this question to the Power BI community @ community.powerbi.com

    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, October 28, 2015 12:48 AM

All replies

  • Tuesday, July 14, 2015 7:21 PM
  • Hi Seth,

    Actually My query different, I want to consolidate data of all sheet filled by multiple people in

    same format sheet. I need to copy all the filled details from all the files which I received from different peoples

    and paste in Column I to L.

    Note: Suppose At first I copy the file of 1st person and paste in master sheet. After this when I go to second file then I same copy the data from that file from column I to L and paste in same range in my MAster sheet but I don't want to replace the fields which already filled in previous step.

    Regards,

    Rajender

    Wednesday, July 15, 2015 9:03 AM
  • Yeah, you would be better off using Access or SQL to consolidate your data since they support true INSERT functionality for tables and then linking your Power Query to that.  One other idea and I don't see any example data so not sure if this will work.

    1) you create one table just containing the dates from earliest to far into the future

    2) you import every new set of data into a new power pivot table

    3) you link the dates for a record in the newly imported table to the dates in the power pivot table containing the dates

    so the backbone of your whole data is the dates table, while your tables keeping the actual data are treated as lookup tables.

    this is hacky and I didn't try it, but it should work

    Wednesday, July 15, 2015 1:20 PM
  • Hi Rajender,

    Yes,Consolidation of excel files is very much possible using PowerQuery but i assume that all the excel file in your case are having the same templates.

    You need to go to PowerQuery-->From File (here you can select the folder option if all the files are stored in this path).

    Once the above step is done it will open the Power Query window and list down the files stored in the directory.

    Click on the double arrow on the Content column this should helo you to consolidate all the data

    we can also consolidate a single sheet and then have a function which will accep the file name as a parameter and  repeat the transformation for other files the file name

    Regards

    Ramanathan

    Wednesday, July 15, 2015 2:42 PM
  • Hi Rajender :-)

    Please give us sample files because now it is reading tea leaves ;-)

    Wednesday, July 15, 2015 2:55 PM
  • The issue, as I understand it, is that reading information in from Power Query refreshes the entire table in the data model and that is not what is desired. What is desired is to incrementally add rows to a table in the data model. So, it is not a consolidation of files or an append query that adds queries together because he would have to keep all of the files forever. What is desired is similar to the INSERT function in SQL, where rows can be inserted into a table and leave the existing data intact.

    That simply isn't how Power Query is designed to work, in SQL terms, it is designed to essentially do a DROP TABLE, and then import the schema and data. That is an over simplification but essentially what it is doing. It is probably more accurately described as a DELETE all rows and re-import given the problems it has once you add calculated fields and then attempt to do a re-import from a data file whose schema has changed.


    Wednesday, July 15, 2015 3:10 PM
  • Hi Seth,

    Thanks a lot for your valuable comments.

    Actually I am not aware of SQL language :-( , recently I got to know about Power query option which is very powerful and we can do anything with the data arrangements.

    If possible if you can share any VBA solution for the same ?

    For example . I put all the files in one folder than in master file I run the macro it will lookup for those specific ranges in all the file and copy those ranges and paste here in master file without replacing the cells which are already filled.

    Thanks in advance for your valuable feedback.

    Regards,

    Rajender

    Thursday, July 23, 2015 10:42 AM
  • Are you putting these into a table in Excel or in the data model in Power Pivot? The reason I ask is that it makes a difference in terms of the VBA.
    Thursday, July 23, 2015 11:20 AM
  • Hi Seth,

    Currently the data is like normal format.

    Whatever according to you is better I will do the same :-). 

    Regards,

    Rajender

    Thursday, July 23, 2015 3:17 PM
  • I'll get to the VBA in a minute. I want to make sure that Append Query won't work however.

    I want to make sure that the other methods will not work for you first. If you will always have all of the data that you want in your files, you could use either Folder or Append Query. If this is kind of a "period" thing where the files only contain the data for that period, but you want to preserve all of the data from the previous periods, probably won't work so well and have to go the VBA route or something else.

    What I did was create a folder and put two CSV files in it with the first one being:

    • Column1
    • one
    • two
    • three

    and the second one

    • Column1
    • four
    • five
    • six

    Created the following folder query:

    let
        Source = Folder.Files("C:\Temp\Append\import"),
        #"Combined Binaries" = Binary.Combine(Source[Content]),
        #"Imported CSV" = Csv.Document(#"Combined Binaries",null,null,null,1252),
        #"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}}),
        #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
        #"Removed Top Rows" = Table.Skip(#"Removed Duplicates",1)
    in
        #"Removed Top Rows"

    Ran this and got six rows. Created a third CSV as follows:

    • Column1
    • seven
    • eight
    • nine

    Ran the query again and got 9 rows. So, if you keep all of your files for all of your periods or if the data supplied has all of the history in them, this method should work as long as they are all in the same format.

    The Append Query works a little different and probably isn't as flexible as the Folder method in terms of adding file but has the flexibility to support different formats in the files because you can make them the same format using m code so that when you append them together they are in the same format.

    For Append, here are the queries:

    person1

    let
        Source = Csv.Document(File.Contents("C:\Temp\Append\import\person1.csv"),[Delimiter=",",Encoding=1252]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type")
    in
        #"Promoted Headers"

    person2

    let
        Source = Csv.Document(File.Contents("C:\Temp\Append\import\person2.csv"),[Delimiter=",",Encoding=1252]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type")
    in
        #"Promoted Headers"

    Append1

    let
        Source = Table.Combine({person1,person2})
    in
        Source

    To add "person3", create a new query person3

    let
        Source = Csv.Document(File.Contents("C:\Temp\Append\import\person3.csv"),[Delimiter=",",Encoding=1252]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type")
    in
        #"Promoted Headers"

    And then modify Append query to the following:

    let
        Source = Table.Combine({person1,person2,person3})
    in
        Source

    If neither of these are going to work in your specific circumstance I'll take a look at what can be done in VBA code.

    Thursday, July 23, 2015 5:26 PM
  • Did you ever get this answered to your satisfaction?
    Thursday, July 30, 2015 10:59 PM
  • Hi Seth,

    I tried, but not much successful :-(.

    Regards,

    Rajender

    Saturday, August 8, 2015 1:26 PM
  • I'd encourage you to take this question to the Power BI community @ community.powerbi.com

    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, October 28, 2015 12:48 AM