none
Expression.Error: We cannot convert the value "[Table]" to type Table. Details: Value=[Table] Type=Type RRS feed

  • Question

  • Hello

    I'm trying to join tables based on condition but my code on the last part when combining tables drops me this error:

    "Expression.Error: We cannot convert the value "[Table]" to type Table. Details:     Value=[Table]     Type=Type"

    Could you please review my code and tell me where my logic is wrong ?

    let
        Source = #"some table",
    
    //Group table
    #"Grouped Rows"=Table.Group(#"some data",{"InLevel"},{{"Data", each _, type table}}),
    
    
    //Separate tables into levels
    tab1=#"Grouped Rows"{[InLevel=null]}[Data],
    tab2=#"Grouped Rows"{[InLevel=1]}[Data],
    tab3=#"Grouped Rows"{[InLevel=2]}[Data],
    tab4=#"Grouped Rows"{[InLevel=3]}[Data],
    
    //joining tables with corresponding levels
    tab1nest=Table.NestedJoin(tab1,{"condition1", "condition2"},#"other table", {"condition1", "condition2"},"othertab", JoinKind.LeftOuter),
    tab2nest=Table.NestedJoin(tab2,{"condition1", "condition2", "condition3"},#"other table", {"condition1", "condition2", "condition3"},"othertab", JoinKind.LeftOuter),
    tab3nest=Table.NestedJoin(tab3,{"condition1", "condition2", "condition4"},#"other table", {"condition1", "condition2", "condition4"},"othertab", JoinKind.LeftOuter),
    tab4nest=Table.NestedJoin(tab4,{"condition1", "condition2", "condition5"},#"other table", {"condition1", "condition2", "condition5"},"othertab", JoinKind.LeftOuter),
    
    
    //expanding needed columns
    exp0=Table.ExpandTableColumn(tab1nest, "othertab", {"othercolumn1", "Name"}, {"othertab.othercolumn1", "othertab.Name"}),
    exp1=Table.ExpandTableColumn(tab2nest, "othertab", {"othercolumn1", "Name"}, {"othertab.othercolumn1", "othertab.Name"}),
    exp2=Table.ExpandTableColumn(tab3nest, "othertab", {"othercolumn1", "Name"}, {"othertab.othercolumn1", "othertab.Name"}),
    exp3=Table.ExpandTableColumn(tab4nest, "othertab", {"othercolumn1", "Name"}, {"othertab.othercolumn1", "othertab.Name"}),
    
    //combine
    expall=Table.Combine({exp0, exp1, exp2, exp3})
    
    in
        expall

    Wednesday, March 9, 2016 10:44 AM

Answers

  • This looks like a consequence of the data privacy feature, though it shouldn't actually trigger when there's only a single data source. You may need to enable "Fast Combine" for this to work.
    • Marked as answer by Exanimis Wednesday, March 9, 2016 3:05 PM
    Wednesday, March 9, 2016 2:09 PM

All replies

  • Very unlikely that this comes from the last step (expall) as it looks perfectly alright and the variables it refers to exist in the query text you've pasted. Have you checked all fields in your intermediate steps? Maybe there's an error there already that doesn't show up with a message on the screen?

    What about #"other table" (in tabxnest)? Is this an external table?


    Imke Feldmann TheBIccountant.com

    Wednesday, March 9, 2016 11:02 AM
    Moderator
  • It shows  the error only when I click on the last step in "Applied steps"
    But if i click "Go to Error" on that screen it points out to tab1nest step.

    Yes #"other table" is a seperate query in this worksheet with the same name.

    Wednesday, March 9, 2016 11:10 AM
  • This looks like a consequence of the data privacy feature, though it shouldn't actually trigger when there's only a single data source. You may need to enable "Fast Combine" for this to work.
    • Marked as answer by Exanimis Wednesday, March 9, 2016 3:05 PM
    Wednesday, March 9, 2016 2:09 PM
  • Thanks... I've changed it and now loads without a problem.

    I've read something about this when looking on the error but it was in Power BI.But I didn't know the same setting is in the excel power query.

    In my case the two tables are two different SQL servers so different source i guess.

    • Edited by Exanimis Wednesday, March 9, 2016 3:41 PM
    Wednesday, March 9, 2016 3:07 PM
  • "I've changed it and now loads without a problem" - what did you change? I am having the same error.
    Wednesday, February 21, 2018 9:20 PM