Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AAAAOps
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

5 REPLIES 5
Anonymous
Not applicable

Hi

Need some quick help pl as new here. Am getting similar error. 

Learning to replicate as in https://www.youtube.com/watch?v=STjBoS1rQuQ&list=LL&index=11&t=311s 

Pl do redirect to a tutorial if any

Reached half way to create fx in pbi:

= (URL) as table =>
let
Source = Web.Page(Web.Contents([URL]))
in
#"fxMovies"

 

and 

let
Source = Excel.Workbook(File.Contents("C:\Users\ssh130\OneDrive\pbi\linked.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "URL"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "1", each fxMovies([URL])),
#"1" = #"Added Custom"{0}[1]
in
#"1"

{797D4A96-898A-4B94-8ECA-E1DE62D9973A&#125;.png.jpg{7539FE2A-1B91-4B38-96CF-782B7882A34E&#125;.png.jpg

MarcelBeug
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)

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.