Power Query slows down after merging queries or tables
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: Power Query slows down after merging queries or tables
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Apr 2014
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query slows down after merging queries or tables

    Just to make sure I understand as the file is really slow.

    For prior year

    Prior Year Query code


    Code:
    let
    Source = Folder.Files("C:\Users\..........................."),
    Key = Table.AddKey(#"Added Custom", {"Location"}, true),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Prior Year", each #"Transform File from Prior Year"([Content])),
    For Current year



    Code:
    let
    Source = Folder.Files("C:\Users\................"),
    Key = Table.AddKey(#"Delete some disposed items", {"ommon System Number"}, true),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Current Year", each #"Transform File from Current Year"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    Exactly like this followed by the remaining coding?
    Also, what's the logic on selecting which field to be the Key? The Common System number is a unique value in both queries. It seems I'm using the system number in one and the location in another.

    I'm just trying to get a better understanding of your thought process.

    Also I'm not understanding this part "if it doesn't work faster try Key for: #"Merged Queries1"" where would I put this?

    Thanks
    Last edited by bobby_smith; Jul 4th, 2019 at 01:50 PM.

  2. #12
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,678
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Power Query slows down after merging queries or tables

    first refresh thread and re-read first line in post#10

    Code:
    let
     Key = Table.AddKey(#"Added Custom", {"Location"}, true),
     Source = Folder.Files("C:\Users\..........................."),
     #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    Code:
    let
     Key = Table.AddKey(#"Delete some disposed items", {"ommon System Number"}, true),
     Source = Folder.Files("C:\Users\................"),
     #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    Also I'm not understanding this part "if it doesn't work faster try Key for: #"Merged Queries1"" where would I put this?
    in Prior you've two merges so if first doesn't work well try Key for the next merge:

    replace with previous key:
    Code:
     Key = Table.AddKey(#"Expanded Location_Table", {"Common System Number"}, true),
    edit:
    you need to test time (4 possibilities) how it works with single key in one query, one key for each query
    btw. your code is a mish-mash to me, not optimized, sorry
    Last edited by sandy666; Jul 4th, 2019 at 02:07 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #13
    Board Regular
    Join Date
    Apr 2014
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query slows down after merging queries or tables

    Thanks. I"ll try this. I did not create the code by writing/coding. All I did mostly was using the point and click feature. I'm still learning power query and I'm not advance enough to be writing full code in the M language.

    I'm truly thankful for your assistance thus far.

  4. #14
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,678
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Power Query slows down after merging queries or tables

    You are welcome

    also you can try

    Code:
    in
     Table.Buffer(#"Merged Queries1")
    Code:
    in
     Table.Buffer(#"Expanded Location_Table")
    but sometimes it makes query slower than faster so you need to test it.

    btw.
    on 1 000 000 rows Table.Buffer changed refresh time from 15 secs to 2.5 minutes so be careful
    Last edited by sandy666; Jul 4th, 2019 at 02:17 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #15
    Board Regular
    Join Date
    Apr 2014
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query slows down after merging queries or tables

    The key appears to work, but I'll not fully know until tomorrow.

    Can you help me understand the primary key please. When I researched it, the syntax was
    Code:
    Table.AddKey(table as table,  columns as list,  isPrimary as logical) as table
    The table is the name of table and column is the column with the key.

    The code you gave me (ex the current year) appears to use table name as #"Delete some disposed items". Can you create any table name and use it?
    Also, when you created the key for the Prior year, you used the table "#Added Custom" and then you used location as the column with the primary key.
    Whats the logic on selecting which column to be the primary key? Should that column contain unique values?

    Lastly, could I have use the column "Common System Number" as the primary key for both queries?

    Thank you for your patience in responding to my questions as I'm truly trying to understand what is being done so I can get better at power query.

    Thanks

  6. #16
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,678
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Power Query slows down after merging queries or tables

    Quote Originally Posted by bobby_smith View Post
    Should that column contain unique values?
    this is the best situation, merge reading each row so if there is more duplicates it will take more time
    also you can use Remove Duplicates from this column, instead of the Key but from practice on my files Key+RemoveDuplicates works faster
    (you need to know what are you doing )

    Quote Originally Posted by bobby_smith View Post
    could I have use the column "Common System Number" as the primary key for both queries?
    as I said you've 4 possibilities, this is just 4th option

    Prior
    Code:
    Key = Table.AddKey(#"Expanded Location_Table", {"Common System Number"}, true),
    Current
    Code:
    Key = Table.AddKey(#"Delete some disposed items", {"Common System Number"}, true),
    there is no any Golden Advice, you must test it yourself.

    edit:
    I forgot to add simple example:

    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Type = Table.TransformColumnTypes(Source,{{"City", type text}, {"Name", type text}, {"Date", type date}})
    in
        Type
    Code:
    // Table2
    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        Type = Table.TransformColumnTypes(Source,{{"City", type text}, {"Name", type text}, {"Date", type date}})
    in
        Type
    Code:
    // Merge1
    let
        Key = Table.AddKey(Table1, {"City"}, true),
        Source = Table.NestedJoin(Table1,{"City"},Table2,{"City"},"Table2",JoinKind.Inner),
        Expand = Table.ExpandTableColumn(Source, "Table2", {"City", "Name", "Date"}, {"Table2.City", "Table2.Name", "Table2.Date"})
    in
        Expand
    Last edited by sandy666; Jul 4th, 2019 at 03:30 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  7. #17
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,678
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Power Query slows down after merging queries or tables

    also you can try InnerJoin instead of LeftJoin

    but as I said: test it yourself
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  8. #18
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,678
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Power Query slows down after merging queries or tables

    Quote Originally Posted by bobby_smith View Post
    Can you help me understand the primary key please. When I researched it, the syntax was
    Code:
    Table.AddKey(table as table,  columns as list,  isPrimary as logical) as table
    Whats the logic on selecting which column to be the primary key? Should that column contain unique values?
    Definition - What does Primary Key mean?

    A primary key is a special relational database table column (or combination of columns) designated to uniquely identify all table records.

    A primary key’s main features are:
    • It must contain a unique value for each row of data.
    • It cannot contain null values.

    A primary key is either an existing table column or a column that is specifically generated by the database according to a defined sequence.

    The primary key concept is critical to an efficient relational database. Without the primary key and closely related foreign key concepts, relational databases would not work.

    Almost all individuals deal with primary keys frequently but unknowingly in everyday life. For example, students are routinely assigned unique identification (ID) numbers, and all U.S. citizens have government-assigned and uniquely identifiable Social Security numbers.

    For example, a database must hold all of the data stored by a commercial bank. Two of the database tables include the CUSTOMER_MASTER, which stores basic and static customer data (name, date of birth, address, Social Security number, etc.) and the ACCOUNTS_MASTER, which stores various bank account data (account creation date, account type, withdrawal limits or corresponding account information, etc.).

    To uniquely identify customers, a column or combination of columns is selected to guarantee that two customers never have the same unique value. Thus, certain columns are immediately eliminated, e.g., surname and date of birth. A good primary key candidate is the column that is designated to hold Social Security numbers. However, some account holders may not have Social Security numbers, so this column’s candidacy is eliminated. The next logical option is to use a combination of columns, such as adding the surname to the date of birth to the email address, resulting in a long and cumbersome primary key.

    The best option is to create a separate primary key in a new column named CUSTOMER_ID. Then, the database automatically generates a unique number each time a customer is added, guaranteeing unique identification. As this key is created, the column is designated as the primary key within the SQL script that creates the table, and all null values are automatically rejected.

    The account number associated with each CUSTOMER_ID allows for the secure handling of customer queries and also demonstrates why primary keys offer the fastest method of data searching within tables. For example, a customer may be asked to provide his surname when conducting a bank query. A common surname (such as Smith) query is likely to return multiple results. When querying data, utilizing the primary key uniqueness feature guarantees one result.

    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •