Forum Discussion
A Best practice to Add column then Nested Join then Expand Table in 1 step/query ?
- Anonymous5 years ago
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 mq1q2But maybe the point of your question is to have better performance.
If so, in order to answer you, we need more info.
- Anonymous5 years ago
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 ..
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."
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 ..
- SaaM5 years ago
Helper II
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
- Anonymous5 years agoNot applicable
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- SaaM5 years ago
Helper II
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