Column in Power Query but not in Power BI Report Dynamics Direct Query

[CQ9]

Hi,

First time post but have used this resource quite a bit in the past - but this one has me stumped so here goes!

I have a Power BI reports that are a direct access via dataverse to Dynamics.

I can see columns in Power Query, but I can not access these in Power BI reports builder see example below:

Power Query:

Power BI:

I have tried various solutions to previous similar issues such as refresh in Power Query/BI and starting a new Report, Sorting and refreshing in Power Query etc etc but the result are the same the columns are not available in Power BI reports.

Just for context  some of these columns in Dynamics are customer created.

Any thoughts on how to overcome this?

TIA!

[CQ9]

Hi Alexis,
No there are no steps removing or selecting specific columns – the only thing I have is I have added a custom column to provide a link to the record:
Here’s the steps from the advanced editor:
let
Source = CommonDataService.Database("******.crm4.dynamics.com"),
dbo_****** = Source{[Schema="dbo",Item="******"]}[Data],
#"Added Custom" = Table.AddColumn(dbo_******, "Link To Record", each "https://******.crm4.dynamics.com/main.aspx?appid=******&pagetype=entityrecord&etn=******s&id="&[*******]&""),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Link To Record", type text}, {"createdon", type date}})
in
#"Changed Type"

Even when I start from scratch, with a new project with no query changes, no link to record etc at all I still can't see the columns to select in the report side - is there limit to columns that come across or if the database has new columns added these don’t show up or load to power BI report side?

[AlexisOlson]

This sounds like some sort of bug.

Can you reproduce it with a file you're able to share? I don't think I can help further without actually having a pbix to poke at.

[CQ9]

HI Alexis,

Thanks for the offer but as this is a direct query I'm not sure what I can do around this - i'll try an import and see if I can rule anything out at this point and post here how I get on. Thanks for the help!

[AlexisOlson]

The first thing I would check is if those columns are hidden.

https://radacad.com/what-fields-to-hide-in-your-power-bi-solution

[CQ9]

Hi Alexis,

Thanks for coming back but there are no hidden columns from the data model:

[AlexisOlson]

Hmmm. Not sure what's going on.

Are there any steps in the query editor removing columns or selecting specific ones?