We cannot convert a value of type List to type Table

[dhooger]

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 TabularEditor and process, I get "We cannot convert a value of type List to type Table".

I'm a touch overmatched in M here to understand how it works. I've attached a link to OneDrive to the dimension here.

I believe the culprit is here...

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

#"Expanded MDM Holiday" = Table.ExpandTableColumn(#"Merged Queries", "MDM Holiday", {"dayoff"}, {"MDM Holiday.dayoff"}),
 #"Replaced Value" = Table.ReplaceValue(#"Expanded MDM Holiday",null,"Workday",Replacer.ReplaceValue,{"MDM Holiday.dayoff"}),
 #"Renamed Columns1" = Table.RenameColumns(#"Replaced Value",{{"MDM Holiday.dayoff", "DayOff"}})

But I don't know what or how to fix it.

[dhooger · Accepted solution]

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]

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]

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]

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.