none
Variable for Table Name RRS feed

  • Question

  • I'm trying to achieve something that seems like it should be fairly simple but I can't find an answer for... replace the name of a table or power query with a variable.

    Currently trying to do this with a merge query so it would look something like this:

    Table.NestedJoin(VARIABLE1,key1,VARIABLE2,key2,"Append",JoinKind.Inner)

    Currently getting all sorts of errors no matter what I try...

    Thank you!

    Friday, January 31, 2020 10:37 PM

Answers

  • Hi Anthony,

    I haven't reviewed the whole case, just your last post (but what you got is the list of the names of the tables). Assuming this is the case, transform that list using what Imke posted earlier (works here):

    let
        ListOfTableNames={"Table2","Table3"},
        ListOfTables = List.Transform(ListOfTableNames, each Record.Field(#sections[Section1], _)),
        CombinedTables = Table.Combine(ListOfTables)
    in
        CombinedTables

    Makes sense (from a non-M master at all :)? 


    Thursday, February 6, 2020 1:49 PM

All replies

  • Could you please post your full code. Your syntax is correct, and I do not understand what is not working.
    Saturday, February 1, 2020 9:52 PM
  • Yeah, I feared that it might be a bit more complex and I'm still fairly new at using Power Query/M Code.

    I'm not really looking to do a function - hoping for users to utilize as easy as possible so they would be able to update a named table in the workbook, refresh, and then get a table as an output. Below is my current code. My Region code replacements worked fine, but the Days replacements don't - I need each day (Monday-Thursday) to be replaced with my day variables (StartDay, Day2, etc.). Each of those has a separate text query referring back to the excel workbook inputs, and each of them should pull up a query based on the text (ex: StartDay = Monday so should pull the Monday query). This is the error I get, assuming that it is reading it as text "Monday" and not query Monday.

    Expression.Error: We cannot convert the value "Monday" to type Table.
    Details:
        Value=Monday
        Type=Type

    let
        ANDOriginCode = OriginRegion,
        ANDDestinationCode = DestinationRegion,
        ANDStartDay = StartDay,
        ANDDay2 = Day2,
        ANDDay3 = Day3,
        ANDDay4 = Day4,
        ANDDay5 = Day5,
        Source = Table.NestedJoin(Monday,{"Tuesday Destination Region Code"},Tuesday,{"Tuesday Origin Region Code"},"Append1 (3)",JoinKind.Inner),
        #"Filtered Rows1" = Table.SelectRows(Source, each [Monday Origin Region Code] = OriginRegion),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"ID", "Pickup Day of Week", "Delivery Day of Week"}),
        #"Expanded Append1 (3)" = Table.ExpandTableColumn(#"Removed Columns", "Append1 (3)", {"Tuesday Origin Region Code", "Wednesday Destination Region Code", "Tuesday Projected Number of Loads"}, {"Tuesday Origin Region Code", "Wednesday Destination Region Code", "Tuesday Projected Number of Loads"}),
        #"Merged Queries" = Table.NestedJoin(#"Expanded Append1 (3)",{"Wednesday Destination Region Code"},Wednesday,{"Wednesday Origin Region Code"},"Append1 (4)",JoinKind.Inner),
        #"Expanded Append1 (4)" = Table.ExpandTableColumn(#"Merged Queries", "Append1 (4)", {"Wednesday Origin Region Code", "Thursday Destination Region Code", "Wednesday Projected Number of Loads"}, {"Wednesday Origin Region Code", "Thursday Destination Region Code", "Wednesday Projected Number of Loads"}),
        #"Merged Queries1" = Table.NestedJoin(#"Expanded Append1 (4)",{"Thursday Destination Region Code"},Thursday,{"Thursday Origin Region Code"},"Append1 (5)",JoinKind.Inner)
    in
        #"Merged Queries1"

    Wednesday, February 5, 2020 12:59 AM
  • Can you share your table Monday?

    Both the code if it is a query or the columns if an excel table.

    Monday (or #"Monday", it is the same) needs to be the name of a table known by power query. Here power query does not find out any table named "Monday" .

    Wednesday, February 5, 2020 6:12 AM
  • Further, it would help to provide a quick extract of your data source, and what result you are expecting. (First rows with headers): There might be other way to achieve what you want.

    Wednesday, February 5, 2020 6:16 AM
  • Could it be that you mean using a parameter to reference the table?

    In Excel, this could be done like so: 

    Record.Field(#sections[Section1], <YourParameterGoesHere>)

    But it doesn't work in Power BI unfortunately.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Wednesday, February 5, 2020 9:23 AM
    Moderator
  • Record.Field(#sections[Section1], <YourParameterGoesHere>)

    Ah ah Imke (Hi)

    Didn't know that "trick" and ike it :). Thanks much again for sharing your knowledge here. Nice day...

    Wednesday, February 5, 2020 10:34 AM
  • Apologies for any confusion. The above code works as is. What doesn't work is when I try to replace Monday with StartDay and then Tuesday with Day2, etc. The text queries (OriginRegion, StartDay, etc.) read tables from the workbook based on user input. The table queries Sunday-Saturday are data tables which need to be combined based on input for the day of week. For example: If a user types Monday, Tuesday, Wednesday, Thursday in the table, the Output query needs to combine those 4 tables. If they type Thursday, Friday, Saturday, then the Output query needs to combine those 3.

    This is the StartDay code which is the same for Day2-7 just reading different rows. I'm confident that the issue is that Power Query is treating it as text rather than a table name, but I'm not sure how else to work around it.

    let
        Source = Excel.CurrentWorkbook(){[Name="DaysTable"]}[Content],
        ANDStartDay = Record.Field(Source{0},"Days")
    in
        ANDStartDay
    Wednesday, February 5, 2020 9:06 PM
  • You want to Combine a set of tables according what your user selected.

    To do it, you need the list of tables to Combine, but what you got is the list of the names of the tables.

    I do not know how to do it. Maybe the masters of M will be able to help you.

    Thursday, February 6, 2020 8:33 AM
  • Hi Anthony,

    I haven't reviewed the whole case, just your last post (but what you got is the list of the names of the tables). Assuming this is the case, transform that list using what Imke posted earlier (works here):

    let
        ListOfTableNames={"Table2","Table3"},
        ListOfTables = List.Transform(ListOfTableNames, each Record.Field(#sections[Section1], _)),
        CombinedTables = Table.Combine(ListOfTables)
    in
        CombinedTables

    Makes sense (from a non-M master at all :)? 


    Thursday, February 6, 2020 1:49 PM
  • Nice,

    KSontag, if lz solution suits you, can you please mark it as an answer

    Thursday, February 6, 2020 4:48 PM
  • if solution suits you, can you please mark it as an answer

    Hey Anthony

    Didn't suit obviously otherwise we would have been told ;-)

    Monday, February 17, 2020 9:35 AM