Forum Discussion

dhooger's avatar
dhooger
Icon for Helper I rankHelper I
4 years ago
Solved

We cannot convert a value of type List to type Table

I'm trying to convert a PBIX into an SSAS Tabular model, and in the PBIX, we have a date dimension built with M that joins to a real table. It works in PBIX file, but as I try to bring that into Tabu...
General Comment
Need Help
Tutorial Requests
  • dhooger's avatar
    dhooger
    4 years ago

    Because I'm not sure how to use NestedJoin when trying a table built with M with an imported table from SQL, I removed those references from the partition. Then recreated the column with DAX using RELATED. Got the same result. Not sure why Power BI desktop worked differently than SSAS but I have an answer.

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago

Your third term needs to be a table and what you have is a list with a single text element.  Try this intead.

 

#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Date"}, #"MDM Holiday", {"calendar_date"}, "MDM Holiday", JoinKind.LeftOuter),

 

Pat

dhooger's avatar
dhooger
Icon for Helper I rankHelper I
4 years ago

Thank you. Does # mean use as a table?

Now when I process the cube, I receive the following error: 
Failed to save modifications to the server. Error returned: 'Expression in partition 'Date-ab68c4b1-f6aa-4e6a-84fb-16426a81c707' in table 'Date' references unknown variable or function 'MDM Holiday'.

Now MDM Holiday is a table in the Tabular Model that comes in as import, not like this Date dimension that is created with M.

  • dhooger's avatar
    dhooger
    Icon for Helper I rankHelper I
    4 years ago

    So I've figured it out a bit further. I've tried copying in the date table built in M from the PBIX where I had imported tables in Tabular Editor. Thus the M table partition for the date table is unaware of the other tables in Tabular Editor. I'm not quite sure what to do with this or how to figure out how to mix mode an M date table with real tables in one model.