A Best practice to Add column then Nested Join then Expand Table in 1 step/query ?

[SaaM]

Hi All,

I was wondering if there is a better way to make my queries:

 My steps:

1. First I add a column "LotNumbers" to a Table1
2. I join Table1 and Table2 on index
3. I add the column "LotsNumbers" to Table2 by expanding my TableColumn and keeping my created column LotNumbers

    Table1_lotsnumbers = Table.SelectColumns(Table.AddColumn(Table1, "LotNumbers", each Text.Combine(List.Select(Record.FieldValues(Record.FromList({[#"lot1"],[#"lot2"]}, type [Col1 = text,Col2 = text])), each _<> "" and _ <> null))),{"indexTable1", "LotNumbers"}),
    #"NestedJoin" = Table.NestedJoin(Table2, {"indexTable2"}, Table1_lotsnumbers , {"index"}, "Table1", JoinKind.LeftOuter),
    #"NestedJoinedDevelopped" = Table.ExpandTableColumn(#"NestedJoin", "BDD", {"LotNumbers"}, {"LotNumbers"})

My question is, is it possible to do all my 3 steps in less than 3 queries  without a step  which creates Table1_lotsnumbers Then join THEN expand

I hope my details were clear enough to help me solve the problem.

Kind regards

Saam

[Anonymous · Accepted solution]

By literally meaning your question, the answer is yes. In the sense that from the "aesthetic" point of view it is possible to fit 3 queries within the same query, using nested let ... in blocks.

let
    q1=let
        s1 = List.Random(10),
        #"Converted to Table" = Table.FromList(s1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        ai1 = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1, Int64.Type)
    in
        ai1,

    q2=let
    s2 = {"a".."j"},
    #"Converted to Table" = Table.FromList(s2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ai2 = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1, Int64.Type)
    in
        ai2,

    mq1q2=let
    Source = Table.NestedJoin(q2, {"Index"}, q1, {"Index"}, "Query1", JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(Source, "Query1", {"Column1"}, {"Column1.1"})
    in
        #"Expanded Query1"

in
    mq1q2

But maybe the point of your question is to have better performance.

If so, in order to answer you, we need more info.

[SaaM]

Hi @edhans , @Anonymous 

Thanks for your remarks.

here is my data:

Table1:

Item Index SerialNumbers LotNumbers
A 1 A123-A223-A333 9856
A 2 A123-A223-A333 40504
A 3 A123-A223 521AA
A 4 A123 212321
B 5 B123-B333 21523
B 6 B123-B333 15321
B 7 B123 51325
C 8 C111 1523
C 9 C111 5231
D 10 D012 4561D

Table2:

Index Product
1 E112
2 E114
3 E115
4 E001
5 E256
6 E536
7 E986
8 E875

Final result wanted is:

Index Product Result:SerialNumbers+LotNumbers
1 E112 A123-A223-A333-9856
2 E114 A123-A223-A333-40504
3 E115 A123-A223-521AA
4 E001 A123-212321
5 E256 B123-B333-21523
6 E536 B123-B333-15321
7 E986 B123-51325
8 E875 C111-1523

as explained by @Anonymous,  it is possible to fit 3 queries within the same query, using nested let ... in blocks.

is it possible to have better performance ? (My original tables contains both of them 12000 rows and 55 columns.)

I hope I am more clearer now.

Kind regards

Saam

PS: I tried to put data in table but its not working, i keep getting this error message:

"Correct the highlighted errors and try again."

[Anonymous · Accepted solution]

Query for tab1:

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY9RDoQgDETv0m83YVqK+glyC8P9r2EL6JrNfrTAzJtJOE/KtBBsMlg+mX2JiAn7ponaMgj+R8SgIT6IvBG7KyPnx43T9S47GN0pztkUz5XRylDDbjf9uNB3dp2u10BYu3HYa7M5APTErHN9/+omj6LqULBVA/yfURMqtXYB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Index = _t, SerialNumbers = _t, LotNumbers = _t]),
    #"Merge di colonne" = Table.CombineColumns(Origine,{"SerialNumbers", "LotNumbers"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Sottoposto a merge")
in
    #"Merge di colonne"

Query for result:

let
    Origine = Table.NestedJoin(Tabella2, {"Index"}, Tabella1, {"Index"}, "Tabella1", JoinKind.Inner),
    #"Tabella Tabella1 espansa" = Table.ExpandTableColumn(Origine, "Tabella1", {"Sottoposto a merge"}, {"Sottoposto a merge"})
in
    #"Tabella Tabella1 espansa"

Try it this way and tell us what run times do you have, for the size you indicated?

maybe first experiment for 1k lines, then for 5k lines etc ..

[SaaM]

I tried your solution, it is working. Thanks @Anonymous  !

For the performance, it takes:

• whole dataset (12k lines):  75 seconds
• 5k lines : 50 seconds
• 1k lines: 50 seconds

I don t have enough experience to judge if it is a good or bad performance.

What do you think of the run times ?

Regards

Saam

[Anonymous]

The time taken if it's good for you is good 😁. Try this to see if it's faster.

let
    
    tab1=Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcyxEQAhCATAXi42EBTFAqyCsf82/g/CTTYCgoYrongtoIWZGAVLTKJ3SRihthKLsFHYxPGCE77/4H0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Product = _t]),

    tfr = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY9RDoQgDETv0m83YVqK+glyC8P9r2EL6JrNfrTAzJtJOE/KtBBsMlg+mX2JiAn7ponaMgj+R8SgIT6IvBG7KyPnx43T9S47GN0pztkUz5XRylDDbjf9uNB3dp2u10BYu3HYa7M5APTErHN9/+omj6LqULBVA/yfURMqtXYB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Index = _t, SerialNumbers = _t, LotNumbers = _t]),
    tab2 = Table.CombineColumns(tfr,{"SerialNumbers", "LotNumbers"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Sottoposto a merge"),

    merged=Table.Join(tab2, {"Index"}, tab1, {"Index"},  JoinKind.Inner)
in
    merged

[SaaM]

@Anonymous 

I got the same performance as the previous query

I will stay with the first one 😉

Thanks for your help and your comprehension of my problems without being very clear ^^

Have a nice WE

[Anonymous · Accepted solution]

By literally meaning your question, the answer is yes. In the sense that from the "aesthetic" point of view it is possible to fit 3 queries within the same query, using nested let ... in blocks.

let
    q1=let
        s1 = List.Random(10),
        #"Converted to Table" = Table.FromList(s1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        ai1 = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1, Int64.Type)
    in
        ai1,

    q2=let
    s2 = {"a".."j"},
    #"Converted to Table" = Table.FromList(s2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ai2 = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1, Int64.Type)
    in
        ai2,

    mq1q2=let
    Source = Table.NestedJoin(q2, {"Index"}, q1, {"Index"}, "Query1", JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(Source, "Query1", {"Column1"}, {"Column1.1"})
    in
        #"Expanded Query1"

in
    mq1q2

But maybe the point of your question is to have better performance.

If so, in order to answer you, we need more info.

[edhans]

You will need to provide some sample data or a file. I am not understanding what you are trying to do with that code snippet and the explanation.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.