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