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 ..
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
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