cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Expression.Error: We cannot convert a value of type Table to type List.

Hello All,

We are completely new to Power BI and trying to integrate with Dynamics 365 Data. We have had some success but are hitting a very confusing stumbling block.

I have imported data from Accounts and Opportunties

I have created a Relationship option set and have been able to merge, expand and rename. its working great, until we try to add a second option set "CustomerHealthOptionSet" and its spitting out the following error;

Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=Table
Type=Type

The two queries for both option sets are almost identical, expect for different data.

See pic below for examples of the two option set tables;

Untitled.jpg

Here is the source code we are using to link this to "Account"

let
    Source = OData.Feed("https://xxxxxxxxxx.api.crm4.dynamics.com/api/data/v8.2/"),
    accounts_table = Source{[Name="accounts",Signature="table"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(accounts_table,{"address1_country", "_new_subcategory_value", "address1_stateorprovince", "aaaa_customerhealthstatus", "_new_category_value", "customertypecode", "statuscode", "territorycode", "aaaa_account_aaaa_customerhealthscore", "aaaa_account_aaaa_customerhealth", "aaaa_account_aaaa_customerhealthscore_Customer", "aaaa_account_aaaa_customerhealthscore_MAT", "new_category", "new_subcategory"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([aaaa_customerhealthstatus] <> null)),
    #"Merged Relationship" = Table.NestedJoin(#"Filtered Rows",{"customertypecode"},RelationshipOptionSet,{"Value"},"NewColumn.1",JoinKind.LeftOuter),
    #"Merged Customer Health" = Table.NestedJoin(#"Removed Other Columns",{"aaaa_customerhealthstatus"},CustomerHealthOptionSet,{"Value"},"NewColumn.2",JoinKind.LeftOuter),
    #"Expanded Relationship" = Table.ExpandTableColumn(#"Merged Relationship", "NewColumn.1", {"Option"}, {"NewColumn.1.Option"}),
    #"Expanded Customer Health" = Table.ExpandTableColumn(#"Merged Customer Health", "NewColumn.2", {"Option"}, {"NewColumn.2.Option"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Relationship", #"Expanded Customer Health",{
    {"NewColumn.1.Option","Relationship"},
    {"NewColumn.2.Option","Customer Health"}
    })

 
in
    #"Renamed Columns"
1 ACCEPTED SOLUTION

At least now I know the cause of the error.

 

In the rename step you provide a table name - #"Expanded Customer Health"  - as the second parameter for function Table.RenameColumns, while the second parameter should be the list with renames (which you provided as the 3rd parameter).

 

Please note that the query will only create 1 table, so if you merge your Accounts with both the RelationshipOptionSet and the CustomerHealthOptionSet, then you will get 1 table that includes the cartesian product of the nested tables.

 

Example:

if, for 1 Account, you have 3 rows in RelationshipOptionSet and 4 records in CustomerHealthOptionSet, then you will end up with 3 x 4 = 12 rows for that Account, after expanding the nested tables.

 

Possibly that is not what you want and you should create 2 queries:

1 query to merge Accounts with RelationshipOptionSet and

1 query to merge Accounts with CustomerHealthOptionSet.

 

If you still want 1 query in which you merge Accounts with both tables, then the query should look like (if I didn't make any mistakes, obviously I can not test):

 

let
    Source = OData.Feed("https://xxxxxxxxxx.api.crm4.dynamics.com/api/data/v8.2/"),
    accounts_table = Source{[Name="accounts",Signature="table"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(accounts_table,{"address1_country", "_new_subcategory_value", "address1_stateorprovince", "aaaa_customerhealthstatus", "_new_category_value", "customertypecode", "statuscode", "territorycode", "aaaa_account_aaaa_customerhealthscore", "aaaa_account_aaaa_customerhealth", "aaaa_account_aaaa_customerhealthscore_Customer", "aaaa_account_aaaa_customerhealthscore_MAT", "new_category", "new_subcategory"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([aaaa_customerhealthstatus] <> null)),
    #"Merged Relationship" = Table.NestedJoin(#"Filtered Rows",{"customertypecode"},RelationshipOptionSet,{"Value"},"NewColumn.1",JoinKind.LeftOuter),
    #"Merged Customer Health" = Table.NestedJoin(#"Merged Relationship",{"aaaa_customerhealthstatus"},CustomerHealthOptionSet,{"Value"},"NewColumn.2",JoinKind.LeftOuter),
    #"Expanded Relationship" = Table.ExpandTableColumn(#"Merged Customer Health", "NewColumn.1", {"Option"}, {"NewColumn.1.Option"}),
    #"Expanded Customer Health" = Table.ExpandTableColumn(#"Expanded Relationship", "NewColumn.2", {"Option"}, {"NewColumn.2.Option"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Customer Health",{
    {"NewColumn.1.Option","Relationship"},
    {"NewColumn.2.Option","Customer Health"}
    })
in
    #"Renamed Columns"

 

Notice that now every step proceeds with the previous step, e.g. step #"Merged Customer Health" now proceeds with #"Merged Relationship" instead of with #"Removed Other Columns" in your code. Etcetera.

Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
Community Champion
Community Champion

It is always good to mention at which step the error (first) occurs, otherwise we have to analyze the entire query.

 

Actually I don't know where this particular issue comes from, but I do notice that you are not always refering to the previous step and I suspect that would cause the issue.

 

We cannot convert a value of type Table to type List.png

Specializing in Power Query Formula Language (M)

Everything seems to be fine until we attempt to rename the columns. If i remove the #expanded customer health and newcolumn.2.option then i dont get any error but also dont get any results.

#"Renamed Columns" = Table.RenameColumns(#"Expanded Relationship", #"Expanded Customer Health",{
    {"NewColumn.1.Option","Relationship"},
    {"NewColumn.2.Option","Customer Health"}
    })
let
    Source = OData.Feed("https://xxxxxxxxxx.api.crm4.dynamics.com/api/data/v8.2/"),
    accounts_table = Source{[Name="accounts",Signature="table"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(accounts_table,{"address1_country", "_new_subcategory_value", "address1_stateorprovince", "aaaa_customerhealthstatus", "_new_category_value", "customertypecode", "statuscode", "territorycode", "aaaa_account_aaaa_customerhealthscore", "aaaa_account_aaaa_customerhealth", "aaaa_account_aaaa_customerhealthscore_Customer", "aaaa_account_aaaa_customerhealthscore_MAT", "new_category", "new_subcategory"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([aaaa_customerhealthstatus] <> null)),
    #"Merged Relationship" = Table.NestedJoin(#"Filtered Rows",{"customertypecode"},RelationshipOptionSet,{"Value"},"NewColumn.1",JoinKind.LeftOuter),
    #"Merged Customer Health" = Table.NestedJoin(#"Removed Other Columns",{"aaaa_customerhealthstatus"},CustomerHealthOptionSet,{"Value"},"NewColumn.2",JoinKind.LeftOuter),
    #"Expanded Relationship" = Table.ExpandTableColumn(#"Merged Relationship", "NewColumn.1", {"Option"}, {"NewColumn.1.Option"}),
    #"Expanded Customer Health" = Table.ExpandTableColumn(#"Merged Customer Health", "NewColumn.2", {"Option"}, {"NewColumn.2.Option"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Relationship", #"Expanded Customer Health",{
    {"NewColumn.1.Option","Relationship"},
    {"NewColumn.2.Option","Customer Health"}
    })
 
in
    #"Renamed Columns"

Could you explain in more detail about refering to the previous step in the next step, as everything seems fine until we try to rename the columns, as you can only #rename columns once per query i assumed you needed to combine into the one call.

Thanks for the quick reply.

At least now I know the cause of the error.

 

In the rename step you provide a table name - #"Expanded Customer Health"  - as the second parameter for function Table.RenameColumns, while the second parameter should be the list with renames (which you provided as the 3rd parameter).

 

Please note that the query will only create 1 table, so if you merge your Accounts with both the RelationshipOptionSet and the CustomerHealthOptionSet, then you will get 1 table that includes the cartesian product of the nested tables.

 

Example:

if, for 1 Account, you have 3 rows in RelationshipOptionSet and 4 records in CustomerHealthOptionSet, then you will end up with 3 x 4 = 12 rows for that Account, after expanding the nested tables.

 

Possibly that is not what you want and you should create 2 queries:

1 query to merge Accounts with RelationshipOptionSet and

1 query to merge Accounts with CustomerHealthOptionSet.

 

If you still want 1 query in which you merge Accounts with both tables, then the query should look like (if I didn't make any mistakes, obviously I can not test):

 

let
    Source = OData.Feed("https://xxxxxxxxxx.api.crm4.dynamics.com/api/data/v8.2/"),
    accounts_table = Source{[Name="accounts",Signature="table"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(accounts_table,{"address1_country", "_new_subcategory_value", "address1_stateorprovince", "aaaa_customerhealthstatus", "_new_category_value", "customertypecode", "statuscode", "territorycode", "aaaa_account_aaaa_customerhealthscore", "aaaa_account_aaaa_customerhealth", "aaaa_account_aaaa_customerhealthscore_Customer", "aaaa_account_aaaa_customerhealthscore_MAT", "new_category", "new_subcategory"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([aaaa_customerhealthstatus] <> null)),
    #"Merged Relationship" = Table.NestedJoin(#"Filtered Rows",{"customertypecode"},RelationshipOptionSet,{"Value"},"NewColumn.1",JoinKind.LeftOuter),
    #"Merged Customer Health" = Table.NestedJoin(#"Merged Relationship",{"aaaa_customerhealthstatus"},CustomerHealthOptionSet,{"Value"},"NewColumn.2",JoinKind.LeftOuter),
    #"Expanded Relationship" = Table.ExpandTableColumn(#"Merged Customer Health", "NewColumn.1", {"Option"}, {"NewColumn.1.Option"}),
    #"Expanded Customer Health" = Table.ExpandTableColumn(#"Expanded Relationship", "NewColumn.2", {"Option"}, {"NewColumn.2.Option"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Customer Health",{
    {"NewColumn.1.Option","Relationship"},
    {"NewColumn.2.Option","Customer Health"}
    })
in
    #"Renamed Columns"

 

Notice that now every step proceeds with the previous step, e.g. step #"Merged Customer Health" now proceeds with #"Merged Relationship" instead of with #"Removed Other Columns" in your code. Etcetera.

Specializing in Power Query Formula Language (M)

View solution in original post

Thanks for this, great response and it worked even without the testing. This has developed my understanding too.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors