Regarding let and in statements in power query

[Kiruthiga]

Hello Friends,

I appreciate your time, I am new to Power Bi and trying to execute the below query but the result is same as what it appear in the query itself than its values. 

let tablevalue = SIGHTS_Schema{[Name="BRIDGE_KEY_SS",Kind="Table"]}[Data] in tablevalue. But when I try with out let like below its working perfectly and rendering the result.

= SIGHTS_Schema{[Name="BRIDGE_KEY_SS",Kind="Table"]}[Data] -works good. Could you please let me the problem, I need to embed the query in a variable because I needto so some filtering later in the same.

Appreciate your time and thank you in advance!

Best,

Kiruthiga

[lbendlin · Accepted solution]

too many let's

let
Source = Snowflake.Databases("cimabbo.ku-west-1.snowflakecomputing.com", "dev_power_bi_wh"),
DEV_bbo_DB_Database = Source{[Name = "DEV_bbo_DB", Kind = "Database"]}[Data],
SIGHTS_Schema = DEV_bbo_DB_Database{[Name = "SIGHTS", Kind = "Schema"]}[Data],
BRIDGE_KEY_SS_Table = SIGHTS_Schema{[Name = "BRIDGE_KEY_SS", Kind = "Table"]}[Data],
#"Removed Columns" = Table.RemoveColumns(
BRIDGE_KEY_SS_Table,
{"BRAND_DESC", "RPT_FACT_JOIN_KEY_PG"}
),
factsellingvendor = List.Distinct(TRANS_STORE_UPC_FACT_SS[SELLING_VENDOR]),
#"Filtered Rows" = Table.SelectRows(
#"Removed Columns",
each List.Contains(factsellingvendor, [SELLING_VENDOR])
)
in
#"Filtered Rows"

You will also want to learn about List.Buffer

[Kiruthiga]

Thank you for your time. ,could you please let me know the issue with the below query? It says : error Expression.SyntaxError: Token 'in' expected.

let

    BRIDGE_KEY_SS = let

    Source = Snowflake.Databases("cimabbo.ku-west-1.snowflakecomputing.com","dev_power_bi_wh"),

    DEV_bbo_DB_Database = Source{[Name="DEV_bbo_DB",Kind="Database"]}[Data],

    SIGHTS_Schema = DEV_bbo_DB_Database{[Name="SIGHTS",Kind="Schema"]}[Data],

    BRIDGE_KEY_SS_Table = SIGHTS_Schema{[Name="BRIDGE_KEY_SS",Kind="Table"]}[Data],

    #"Removed Columns" = Table.RemoveColumns(BRIDGE_KEY_SS_Table,{"BRAND_DESC", "RPT_FACT_JOIN_KEY_PG"}),

      factsellingvendor = List.Distinct(TRANS_STORE_UPC_FACT_SS[SELLING_VENDOR]),

       #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each List.Contains(factsellingvendor, [SELLING_VENDOR]))

in

     #"Filtered Rows"

Thank you!!~

[lbendlin · Accepted solution]

too many let's

let
Source = Snowflake.Databases("cimabbo.ku-west-1.snowflakecomputing.com", "dev_power_bi_wh"),
DEV_bbo_DB_Database = Source{[Name = "DEV_bbo_DB", Kind = "Database"]}[Data],
SIGHTS_Schema = DEV_bbo_DB_Database{[Name = "SIGHTS", Kind = "Schema"]}[Data],
BRIDGE_KEY_SS_Table = SIGHTS_Schema{[Name = "BRIDGE_KEY_SS", Kind = "Table"]}[Data],
#"Removed Columns" = Table.RemoveColumns(
BRIDGE_KEY_SS_Table,
{"BRAND_DESC", "RPT_FACT_JOIN_KEY_PG"}
),
factsellingvendor = List.Distinct(TRANS_STORE_UPC_FACT_SS[SELLING_VENDOR]),
#"Filtered Rows" = Table.SelectRows(
#"Removed Columns",
each List.Contains(factsellingvendor, [SELLING_VENDOR])
)
in
#"Filtered Rows"

You will also want to learn about List.Buffer

[Kiruthiga · Accepted solution]

Wow I sincerely appreciate your prompt reply., I didnt notice the intermediate "Let". thanks for your time and that worked!!

Best,

Kiruthiga

[v-mdharahman]

Hi @Kiruthiga,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are facing issue while runing queries and wants to embed it into a variable. As @lbendlin and @ronrsnfld both responded to your query, please go through the responses and mark the helpful reply as solution.

I would also take a moment to thank @lbendlin and @ronrsnfld, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[Kiruthiga]

Appreciate your time! I got it. I am trying to load 2 tables. Table1 has selling_Vendor and table2 should reload only the records for the selling_Vendor which exists in table 1. Then append with table1. So basically Table 1 should have its own data and append the data from table2 with matching selling vendor loaded. This is the first step to load the Table2 with the matching selling_vendor from Table1 .I tried the below query but it has some issue, appreciate your thoughts.

= [A= Table.RemoveColumns(BRIDGE_KEY_VS_Table,{"BRAND_DESC", "RPT_FACT_JOIN_KEY_PG", "RPT_FACT_JOIN_KEY_SEGMENT", "STORE_FMT_DESC",

 "YTD_TY_PROMO_SALES_ECOMM_VENDOR", "YTD_TY_PROMO_QTY_ECOMM_VENDOR",

    "STORE_LVL_3_CD", "STORE_LVL_3_DESC"}),factsellingvendor = List.Distinct(TRANS_STORE_UPC_FACT_SS[SELLING_VENDOR]),

    FilteredTable2 = Table.SelectRows(A,each List.Contains(factsellingvendor,SELLING_VENDOR))][FilteredTable2] Thank you!!

[lbendlin]

List.Intersect - PowerQuery M | Microsoft Learn  is worth a read.

[ronrsnfld]

I am guessing that you are entering your code into the formula bar and not into the Advanced Editor.

Since your first attempt was not preceded by an equal (=) sign, it would have been entered as a text string into the Source step.

Your second attempt is preceded by the equal (=) sign and is entered as code for that first step.

Open the Advanced Editor and you will see better what is going on.

Your first try, in the Advanced Editor:

let
    Source = "let tablevalue = SIGHTS_Schema{[Name=""BRIDGE_KEY_SS"",Kind=""Table""]}[Data] in tablevalue"
in
    Source

Your second try, in the Advanced Editor

let
    Source = SIGHTS_Schema{[Name="BRIDGE_KEY_SS",Kind="Table"]}[Data]
in
    Source

[Kiruthiga]

Appreciate your prompt reply., I didnt notice your message and went out with alternative approach in loading a staging table with only selling_Vendor then load table 2 based on staging table and finally append into table1., let me try the one you suggested above as well .,thanks a lot!!

[lbendlin]

There is no problem.  If you only have a single line of code then you can omit "let" and "in" .  Even with multiple lines of code you can use the record notation   [,,,]  instead.

If you want to learn Power Query all you have to do is consume this primer Power Query M Primer (part 1): Introduction, Simple Expressions & let | Ben Gribaudo