Forum Discussion

CQ9's avatar
CQ9
Regular Visitor
2 years ago

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

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!

 

Need Help

6 Replies

    • CQ9's avatar
      CQ9
      Regular Visitor
      2 years ago

      Hi Alexis,

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

       

      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        2 years ago

        Hmmm. Not sure what's going on.

         

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

  • CQ9's avatar
    CQ9
    Regular Visitor
    2 years ago

    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's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      2 years ago

      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's avatar
        CQ9
        Regular Visitor
        2 years ago

        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!