Forum Discussion
Helper IIA Best practice to Add column then Nested Join then Expand Table in 1 step/query ?
Hi All,
I was wondering if there is a better way to make my queries:
My steps:
- First I add a column "LotNumbers" to a Table1
- I join Table1 and Table2 on index
- 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
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.
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 ..
7 Replies
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.
- edhans
Community Champion
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. 
SaaMHi 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 4561DTable2:
Index Product
1 E112
2 E114
3 E115
4 E001
5 E256
6 E536
7 E986
8 E875Final 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-1523as 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 ..
- 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
