none
How to retrieve self (this) query name? RRS feed

  • Question

  • Hello! Is there a way to determine the name of itself by query that is evaluated? The name that is in query properties we change.

    Thank you.

    Tuesday, December 3, 2019 3:30 PM

Answers

  • Thanx, I solved the problem:

    let
        queries = Record.ToTable( #sections[Section1] ),
        select_marked = Table.SelectRows( queries, each Text.StartsWith( [Name], "AUTO_TABS_PREFIX")),
        choose_self = Table.AddColumn( select_marked, "__Id__", each try if Record.HasFields( Value.Metadata([Value]), "__Id__") then null else [Name] otherwise [Name] ),
        self_name = List.RemoveNulls(choose_self[__Id__]){0},

        res = 0 meta [__Id__ = 555]
    in
        res

    • Marked as answer by Bagration.555 Tuesday, December 3, 2019 7:21 PM
    Tuesday, December 3, 2019 7:09 PM

All replies

  • Hi

    You can get the name of all queries with

    = Table.SelectColumns(Record.ToTable(#sections[Section1]),{"Name"})

    and they will be listed in the order they appear in the Queries pane (left side of Power Query editor)


    So if you only have 1 query

    = Table.SelectColumns(Record.ToTable(#sections[Section1]),{"Name"})[Name]{0}
    OR
    = Table.FirstValue(Table.SelectColumns(Record.ToTable(#sections[Section1]),{"Name"}))

    will give you what you want.

    If you have more than 1 query the question remains and suggestion would be that you explain what you're trying to do as there might be a different way to do it...

    Hope this helps in the meantime 

    • Proposed as answer by anthony34 Wednesday, December 4, 2019 5:11 AM
    Tuesday, December 3, 2019 4:47 PM
  • Thanx for reply. I need to load the table from excel that is with the name of query.

    The reason I need this is following:

    on sheet X1 I have a table named Y1 that is the result of query named Y1 (it is self-updating table).

    Everything works, but if I want to copy sheet X1 to X2, and then rename copied query Y1 (2) to Y2, the table is automatically renamed to query name and it is ok. The problem is that copied query Y2 loads data using "Y1" string (which is the query name) and to make second sheet work I need manually rename this string to Y2's name. If I knew current query's name, I could make automatic this work.

    Tuesday, December 3, 2019 5:02 PM
  • Also, if you have query groups, the order they appear in section is not correct
    Tuesday, December 3, 2019 5:10 PM
  • The reason I need this is following:

    on sheet X1 I have a table named Y1 that is the result of query named Y1 (it is self-updating table).

    Everything works, but if I want to copy sheet X1 to X2, and then rename copied query Y1 (2) to Y2, the table is automatically renamed to query name and it is ok. The problem is that copied query Y2 loads data using "Y1" string (which is the query name) and to make second sheet work I need manually rename this string to Y2's name. If I knew current query's name, I could make automatic this work.

    Not sure there's a solution to this via query given your scenario. Is VBA an option?
    Tuesday, December 3, 2019 5:26 PM
  • Yes, maybe is there a way using VBA to modify connection in the way to correct string in the query? For the functionality I need, it requires that query know it's name
    Tuesday, December 3, 2019 5:56 PM
  • For the functionality I need, it requires that query know it's name

    Not sure I understand what you mean - sorry about that

    You earlier said "I want to copy sheet X1 to X2..." so the scenario is similar to URL issue when creating power query with VBA. Just look at the suggestion I made at the end and check if this can solve your problem

    Tuesday, December 3, 2019 6:17 PM
  • Thanx, I solved the problem:

    let
        queries = Record.ToTable( #sections[Section1] ),
        select_marked = Table.SelectRows( queries, each Text.StartsWith( [Name], "AUTO_TABS_PREFIX")),
        choose_self = Table.AddColumn( select_marked, "__Id__", each try if Record.HasFields( Value.Metadata([Value]), "__Id__") then null else [Name] otherwise [Name] ),
        self_name = List.RemoveNulls(choose_self[__Id__]){0},

        res = 0 meta [__Id__ = 555]
    in
        res

    • Marked as answer by Bagration.555 Tuesday, December 3, 2019 7:21 PM
    Tuesday, December 3, 2019 7:09 PM
  • Now when we duplicate the query, it always knows it's name (self_name)
    Tuesday, December 3, 2019 7:10 PM
  • Each such query that starts from AUTO_TABS_PREFIX prefix knows it's name :)
    Tuesday, December 3, 2019 7:11 PM