Forum Discussion

SaaM's avatar
SaaM
Helper II
5 years ago
Solved

A 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...
  • Anonymous's avatar
    Anonymous
    5 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
        mq1q2

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

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

  • Anonymous's avatar
    Anonymous
    5 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 ..