Nested loop in M Code question

[Anonymous]

I am trying to do a nested loop, and it does not seem to be working. 

I am trying to reference a column from a different Table Table1[Column1] and compare each of the rows in column against each of the rows in the current query [QueryColumn].

The issue is that I need to run some commands on the rows in Table1[Column1] before it is able to be compared to [QueryColumn].

In pseduo code here is what I want to do:

For each Row in [QueryColumn]

        Y = Current Row separated into list, delimited by "." (Y is then a list of row contents)

              For each row in Table1[Column1]

                     X =   Current row Separated into list, delimited by "." (X is then a list of the row contents)

                    if X{0} = Y{0} then X{0} else Y{0}

               Next row X
Next Row Y

This power query stuff is so hard to figure out.

[Anonymous]

I try to stay strictly on the subject of the title.
So I explain, as far as I know, how a nested loop works (or doesn't work) as required.

if you has the folowing table1:

and want to refer this from this other table:

you might be tempted to do an add.column like this (in this case I select only rows, but the concept is valid for any operation you want to do on the table):

you get this result:

what happened? where is the problem?

the answer lies in the advanced editor code:
The two loops the inner and outer loops are controlled by the same implicit variable that the each function refers to and in this context the [ID] column is not defined.

the solution?

change the name (i.e. "_") to another in inner cycle to, for example "inner". 

and you are done:

[edhans]

@Anonymous ,
I think @Anonymous 's code is very clean, but it is a little hard for me to read. I'm still trying to wrap my head around the (stuff here) => syntax and logic.

I approach it (currently) a little differently buy assigning the current field code to a variable then doing a comparison. Same results:

But my M code for that column is a bit more verbose - see the line starting with #"Add Table 1 Data"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWJVkoCsozArGQgyxjMSgGyTJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column2", Int64.Type}}),
    #"Add Table 1 Data" =
        Table.AddColumn(
            #"Changed Type",
            "Table 1 Data",
            each
                let
                    varCurrentCode = [Column1]
                in
            Table.SelectRows(
                Table1,
                each [Column1] = varCurrentCode
            )
        )
in
    #"Add Table 1 Data"

It is for me like avoiding the use of EARLIER in DAX. I just use a variable. I am not sure if either method is more performant than the other. 

[Anonymous]

@edhans 

i think i will use also your technique from now on :-), whenever we need to be clearer

[edhans]

Yeah. I am trying to read @ImkeF's post here on improving performance and the syntax is slowing me down. I am having trouble visualizing what is in each of those (variable) names. I would just like to have more verbose code as a rule assuming it doesn't impact performance, and I don't think declariing variables as I do vs the other way is material in performance, but I could be wrong.

Back to studying.

[v-alq-msft]

Hi, @Anonymous 

You may try using List.Intersect to determine whether two lists have intersection. I created sample data to reproduce your scenario. The pbix file is attached in the end.

Query:

Table1:

You may add a custom column with the following codes to count the rows which has intersections with current list.

= Table.AddColumn(#"Changed Type", "Custom.1", each let list=Text.Split( [QueryColumn],"."),count=
Table.RowCount(
Table.SelectRows(Table1,each List.Count( List.Intersect( {Text.Split([Column1],"."),list}) )>0)
)
in
if count>0 then count else 0
)

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

Yes, and you jumped right in the deep end! 😁
Power Query has two ways to loop through data, List.Generate and List.Accumulate. I recommend you grab a cup of coffee and read this blog by Chris Webb, and maybe even hit a few videos. On a scale of 1-10, both of these functions rate up there at 9 or 10 in complexity and understanding. If you can master these, you have mastered Power Query.

However, I am not certian your solution requires this. Can you post some sample data and what your expected results would be?How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

Sadly, 

I have already review that blog, and watched so many videos... it seems like the syntax of M Code just fails everyone ;(

Yes, sample data!!

Query

-------------------

QueryColumn

1.2.3.4

2.3.4.5

11.15.19.22

1.5.5.3

Table1

1.1.15.3

7.8.8.8

11.33.99.88

Query(after function ran on it)

1	first index matched with first index of 1.1.15
2	Did not match with anything
11	first index Matched with first index of 11.15.19.22
1	first index matched with first index of 1.1.15

Add Column in Query for matching pairs.

So the funtion was able to take split the row into a list {1, 2, 3, 4}, and then compare it against ALL of the Table 1 rows also split as lists....
{1, 1 ,15, 3}

{7, 8, 8, 8}

{11, 33 ,99, 88}

For example:

It takes the first index of each list {0} and compares it against the Table1 lists, one row at a time...
First row of Query[QueryColumn] = {1, 1 , 15 , 30}
{1, 1, 15, 3}{0} does equal {1, 1 ,15, 3}{0}

{1, 1 ,15, 3}{0} does NOT equal {7, 8, 8, 8}{0}

{1, 1 ,15, 3}{0} does NOT equal {11, 33 ,99, 88}

Place matching Table1 list into Column2 of Query

Second row of Query[QueryColumn] = {2, 3 ,4 ,5}

{2, 3 ,4 ,5}{0} does equal {1, 1 ,15, 3}{0}

{2, 3 ,4 ,5}{0} does NOT equal {7, 8, 8, 8}{0}

{2, 3 ,4 ,5}{0} does NOT equal {11, 33 ,99, 88}

No matching Table1 list into Column2 of Query

Third row of Query[QueryColumn] = {11, 15, 19, 22}

{11, 15, 19, 22}{0} does NOT equal {1, 1 ,15, 3}{0}

{11, 15, 19, 22}{0} does NOT equal {7, 8, 8, 8}{0}

{11, 15, 19, 22}{0} does equal {11, 33 ,99, 88}

Place matching Table1 list into Column2 of Query in 11.15.19.22 row