none
Table.RowCount generates errors when expanding a merged table in the next steps RRS feed

  • Question

  • Hi Guys

    I am facing with a strange behaviour with M and would be happy if someone could explain why expanding a column would remove results and generate errors in another column of data (not formula!) that was generated in a previous step.

    I replicated the issue in a simplified example:

    let
        Table0 = #table(
            type table[Col0=text], 
            {{"A"},{"B"},{"C"}}
        )
    in Table0

    let
        Table1 =#table(
            type table[Col1=text, Col2=number],
            {{"A",1},{"A",2},{"B",3}}
        )
    in Table1

    let
        Source = Table0,
        MergeTable1 = Table.NestedJoin(Table0,{"Col0"},Table1,{"Col1"},"Merged Table",JoinKind.LeftOuter),
        AddRowCount = Table.AddColumn(MergeTable1, "Count", each Table.RowCount([Merged Table]), Int64.Type),
        ExpandTable1 = Table.ExpandTableColumn(AddRowCount, "Merged Table", {"Col1", "Col2"}, {"Col1", "Col2"})
    in
        ExpandTable1

    Step 3 "AddRowCount" looks like:


    Step 4 "ExpandTable1" looks like:

    I cannot explain the errors in the "Count" Column.



    Then I tried to play with it a little bit, and managed to get the result I expected by using the mysterious "Table.Buffer" function:

    let
        Source = Table0,
        MergeTable1 = Table.NestedJoin(Table0,{"Col0"},Table1,{"Col1"},"Merged Table",JoinKind.LeftOuter),
        AddRowCount = Table.AddColumn(MergeTable1, "Count", each Table.RowCount([Merged Table]), Int64.Type),
        ExpandTable1 = Table.ExpandTableColumn(Table.Buffer(AddRowCount), "Merged Table", {"Col1", "Col2"}, {"Col1", "Col2"}) //TABLE.BUFFER
    in
        ExpandTable1



    In addition, if there is no Merge step, it works like a charm without the Table.Buffer:

    let
        Source = 
        #table(
            type table[Tables=table], 
            {{Table1},{Table1}}
        ),
        AddRowCount = Table.AddColumn(Source, "Count", each Table.RowCount([Tables]), Int64.Type),
        Expand = Table.ExpandTableColumn(AddRowCount, "Tables", {"Col1", "Col2"}, {"Col1", "Col2"})
    in Expand



    Strange, isn't it ?

    Best, Anthony


    • Edited by anthony34 Thursday, October 26, 2017 11:04 AM
    Thursday, October 26, 2017 10:57 AM

Answers