cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dhooger
Helper I
Helper I

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 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.

 

1 ACCEPTED SOLUTION
dhooger
Helper I
Helper I

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.

View solution in original post

4 REPLIES 4
dhooger
Helper I
Helper I

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.

View solution in original post

mahoneypat
Super User
Super User

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

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.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors