locked
Use different sources without merge RRS feed

  • Question

  • Is it possible to create a single query using different sources in a single query? A would like to import a table in my model that will have to be used in other files in the future. Using merge I have to create 3 queries in each file and then merge into a single one many times. Using a single query would allow me to copy the script and paste in the new files with less effort. Is it possible to writte in the same code ("Let...") a transformation in Source1 merging in an another transformed data in the same file from a different source?
    Saturday, September 17, 2016 4:34 AM

Answers

  • I created a small example appending two lists of movies from boxofficemojo.com - the alltime worldwide top movies and the top movies that never reached number one of the charts. You should be able to copy this to a blank query and get it to work right away:

    let
    // first source: alltime worldwide top movies
        Source1 = Web.Page(Web.Contents("http://www.boxofficemojo.com/alltime/world/")),
        Data01 = Source1{0}[Data],
        ChangedType1 = Table.TransformColumnTypes(Data01,{{"Rank", Int64.Type}, {"Title", type text}, {"Studio", type text}, {"Worldwide", type text}, {"Domestic /%", type text}, {"Domestic /%2", type text}, {"Overseas /%", type text}, {"Overseas /%2", type text}, {"Year^", type text}}),
        WorldwideTopMovies = Table.SelectColumns(ChangedType1,{"Rank", "Title"}),
    // mind the comma after the last line here
    // second source: top movies that never reached nr 1
        Source2 = Web.Page(Web.Contents("http://www.boxofficemojo.com/alltime/domestic/never1.htm")),
        Data02 = Source2{0}[Data],
        ChangedType2 = Table.TransformColumnTypes(Data02,{{"Rank", Int64.Type}, {"OR^", Int64.Type}, {"Title (click to view)", type text}, {"Studio", type text}, {"Top#(cr)#(lf)Rank", Int64.Type}, {"Release#(cr)#(lf)Gross**", type text}, {"Year^", type text}}),
        RemovedOtherColumns2 = Table.SelectColumns(ChangedType2,{"OR^", "Title (click to view)"}),
        TopMoviesNotNr1 = Table.RenameColumns(RemovedOtherColumns2,{{"OR^", "Rank"}, {"Title (click to view)", "Title"}}),
    
    // last step: append the two tables
        AppendedQuery = Table.Combine({WorldwideTopMovies, TopMoviesNotNr1})
    in
        AppendedQuery

    Wednesday, September 28, 2016 1:07 PM

All replies

  • Tell us about the source files please? Are there multiple files in the same format? If so you can use the folder function to get the list of files and create a single parse function to turn each into data tables. In the end, each file in the folder gets added to the result table.

    Your statement "I have to create three queries in each file" has me a little confused wondering if you are separating a big file into small parts and then combining those into a single result file.

    Monday, September 19, 2016 4:33 PM
  • Thank you for the reply.

    Let me be more clear with the clarification of what I want to achieve. The files are not in the same format, is not just an append of data. 

    I have my customer master data query (excel sheet), where I already perform some transformations (have only one name for the customer based on the hierarchy of ship-to, payer, etc.), but at a given point its important for me to merge the customer master data query with another query where I make manual adjustments on customer former code and new code, in order to avoid it appearing as a big lost and a big won at the same time. 

    This second query of adjustments for the first one can either be a tab in the original file or a new file (to make it lighter), but is not an append. In the end I just want not to have to create connections queries for this merge, have everything in only one code that I can copy and paste easily the code in every file where I already have my customer query and will replace for this improved one.

    Tuesday, September 20, 2016 12:47 PM
  • If I understand you well, you want to merge or append multiple sources without ending up with multiple queries, right?

    When you look at a Power Query script in the advanced editor, you'll see M code like

    let

      A = <something>,

      B = <some function on> A,

      C = <some function on> C

    in

      C

    When you have two queries that are put together, it's possible to combine these in the same script, but you'd have to take care that the 'variable names' (A, B, C in the example above) are unique. So

    let

      A_1 = <something>,

      B_1 = <some function on> A_1,

      A_2 = <something else>,

      B_2 = <some function on> A_2,

      C = <merge of> B_1 and B_2

    in

      C

    Not sure if this works all the time, but I've done things like this before.

    Thursday, September 22, 2016 6:25 PM
  • Thank you. It's exactly that. I've found only one example of that (In a microsoft session by the way), and they used two queries to present the function. I will look for examples of a real workbook and query all in one script, but If you could share one (a link) to make it easier, I appreciate a lot. Thanks again for the help. Was very useful to know it's possible.
    Tuesday, September 27, 2016 2:07 AM
  • I created a small example appending two lists of movies from boxofficemojo.com - the alltime worldwide top movies and the top movies that never reached number one of the charts. You should be able to copy this to a blank query and get it to work right away:

    let
    // first source: alltime worldwide top movies
        Source1 = Web.Page(Web.Contents("http://www.boxofficemojo.com/alltime/world/")),
        Data01 = Source1{0}[Data],
        ChangedType1 = Table.TransformColumnTypes(Data01,{{"Rank", Int64.Type}, {"Title", type text}, {"Studio", type text}, {"Worldwide", type text}, {"Domestic /%", type text}, {"Domestic /%2", type text}, {"Overseas /%", type text}, {"Overseas /%2", type text}, {"Year^", type text}}),
        WorldwideTopMovies = Table.SelectColumns(ChangedType1,{"Rank", "Title"}),
    // mind the comma after the last line here
    // second source: top movies that never reached nr 1
        Source2 = Web.Page(Web.Contents("http://www.boxofficemojo.com/alltime/domestic/never1.htm")),
        Data02 = Source2{0}[Data],
        ChangedType2 = Table.TransformColumnTypes(Data02,{{"Rank", Int64.Type}, {"OR^", Int64.Type}, {"Title (click to view)", type text}, {"Studio", type text}, {"Top#(cr)#(lf)Rank", Int64.Type}, {"Release#(cr)#(lf)Gross**", type text}, {"Year^", type text}}),
        RemovedOtherColumns2 = Table.SelectColumns(ChangedType2,{"OR^", "Title (click to view)"}),
        TopMoviesNotNr1 = Table.RenameColumns(RemovedOtherColumns2,{{"OR^", "Rank"}, {"Title (click to view)", "Title"}}),
    
    // last step: append the two tables
        AppendedQuery = Table.Combine({WorldwideTopMovies, TopMoviesNotNr1})
    in
        AppendedQuery

    Wednesday, September 28, 2016 1:07 PM
  • Thank you Michiel.

    I need a Merge, but I believe it will work just fine replacing the Append for the Merger.

    You saved me a lot of time.

    Regards,

    Monday, October 3, 2016 5:18 PM