Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SCWO
New Member

Report builder: (Null) values in query designer when [] brackets in column name

Hi,

I want to build a report with the report builder, connected to a PowerBi semantic model. 

I realized, that when i try to add a dataset, some of my data values are shown as (Null) values. That happens with all data, when the column name contains [ ] brackets. (see image)

 

SCWO_0-1720085598799.png

SCWO_1-1720085641942.png

Does anyone know, how I have to change the query, so that the column and its values can be found?

Or is there any other solution that it will work and I can keep the [] brackets in the column name?

 

Thanks for any hint

 

1 ACCEPTED SOLUTION
hackcrr
Solution Sage
Solution Sage

Hi, @SCWO 

The problem you are experiencing is that when connecting to the Power BI semantic model, the column names contain square brackets ( [ ]), causing (NULL) values to appear in Report Builder, which may be due to the way the Query Designer handles special characters in column names. Here are a few ways to resolve this issue:

Adding Aliases to Columns in a DAX Query
You can add aliases to columns in a DAX query to avoid the use of square brackets. The following is an example of how to add an alias to a column:

EVALUATE 
SUMMARIZECOLUMNS(
    'fctEnergy'[Datum], 
    'fctEnergy'[Abriss [0/1]] AS Abriss,
    'fctEnergy'[Einspeisung Gasturbine] AS Einspeisung_Gasturbine,
    'fctEnergy'[Einspeisung K 07 [kW]] AS Einspeisung_K07_kW
)

If possible, you can rename the columns in Power BI Desktop to remove the square brackets and then publish the updated model. This method ensures that all downstream users and tools that reference these columns do not encounter issues.

In Report Builder, you can create datasets with custom queries and rename columns within them. Here is how to define a query directly in Report Builder:
In Report Builder, go to Dataset Properties.
Select Query Designer and switch to Edit as Text mode.
Write a custom DAX query with aliased column names, similar to the following:

EVALUATE 
SUMMARIZECOLUMNS(
    'fctEnergy'[Datum], 
    'fctEnergy'[Abriss [0/1]] AS Abriss,
    'fctEnergy'[Einspeisung Gasturbine] AS Einspeisung_Gasturbine,
    'fctEnergy'[Einspeisung K 07 [kW]] AS Einspeisung_K07_kW
)

 

hackcrr

If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly

View solution in original post

3 REPLIES 3
d_gosbell
Super User
Super User

Note that you can also "fix" this by right clicking on the field and going into Field properties

d_gosbell_0-1720570102101.png

And they you'll see that the Query Builder has inserted an extra closing square bracket ] - if you remove this the field should start working 

d_gosbell_1-1720570126851.png

 

SCWO
New Member

@hackcrr 

Thank you so much! That's my solution!

Great response, perfectly explained.

hackcrr
Solution Sage
Solution Sage

Hi, @SCWO 

The problem you are experiencing is that when connecting to the Power BI semantic model, the column names contain square brackets ( [ ]), causing (NULL) values to appear in Report Builder, which may be due to the way the Query Designer handles special characters in column names. Here are a few ways to resolve this issue:

Adding Aliases to Columns in a DAX Query
You can add aliases to columns in a DAX query to avoid the use of square brackets. The following is an example of how to add an alias to a column:

EVALUATE 
SUMMARIZECOLUMNS(
    'fctEnergy'[Datum], 
    'fctEnergy'[Abriss [0/1]] AS Abriss,
    'fctEnergy'[Einspeisung Gasturbine] AS Einspeisung_Gasturbine,
    'fctEnergy'[Einspeisung K 07 [kW]] AS Einspeisung_K07_kW
)

If possible, you can rename the columns in Power BI Desktop to remove the square brackets and then publish the updated model. This method ensures that all downstream users and tools that reference these columns do not encounter issues.

In Report Builder, you can create datasets with custom queries and rename columns within them. Here is how to define a query directly in Report Builder:
In Report Builder, go to Dataset Properties.
Select Query Designer and switch to Edit as Text mode.
Write a custom DAX query with aliased column names, similar to the following:

EVALUATE 
SUMMARIZECOLUMNS(
    'fctEnergy'[Datum], 
    'fctEnergy'[Abriss [0/1]] AS Abriss,
    'fctEnergy'[Einspeisung Gasturbine] AS Einspeisung_Gasturbine,
    'fctEnergy'[Einspeisung K 07 [kW]] AS Einspeisung_K07_kW
)

 

hackcrr

If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.