Can I dyanmically set Column Description in Power Query

[rpiboy_1]

I have a data model where the columns of the final query being loaded into Power BI are dynamically renamed and typed from another query. This is due to how the REST API that is being queried functions where the data columns returned are intially using an internal name/ID which is not useful for end users. The table (query) of column names is also used to determine what data is queried from the REST API. In the column table I have a column that I would like to use as the description of the column in Power BI. I've done some research, testing and even a little bit of AI and have yet to come up with a solution that allows me to set the Description (meta data) of the column(s) in the data table.

Is this possible? If so how...?

[V-yubandi-msft]

Hi @rpiboy_1 ,

Could you please confirm if you've submitted this as an idea in the Ideas Forum? If so, sharing the link here would be helpful for other community members who may have similar feedback.

If we don’t hear back, we’ll go ahead and close this thread. For any further discussions or questions, please start a new thread in the Microsoft Fabric Community Forum  we’ll be happy to assist.

Thank you for being part of the Microsoft Fabric Community.

[V-yubandi-msft]

Hi @rpiboy_1 ,

We noticed we haven't received a response from you yet, so we wanted to follow up and ensure the solution we provided addressed your issue. If you require any further assistance or have additional questions, please let us know.

Your feedback is valuable to us, and we look forward to hearing from you soon.

Thank You.

[rpiboy_1]

My feedback is that neither solution is particularly ideal. Using tooltips in this way prevents a report author from then employing tool-tips for their intended purpose while authoring a report. The second option sounds like it is not as simply as doing something in a dataflow, rather a script/programmatic solution would likely be required. I think it would simply be more useful if the Power BI description were exposed in the Power Query model, or if Power BI description could read meta data from the Power Query model. A last resort option would be that description could be set via DAX, but in reality that description should be controlled by the person managing the data model and the transformations, not neccessarily the person who might be building a report.

[V-yubandi-msft · Accepted solution]

Hi @rpiboy_1 ,

Thanks for the  feedback. You are correct. Using tooltips to describe metadata can be a limitation, and defining metadata through scripting or manual effort is a rather complex task. Unfortunately, Power Query does not have an intrinsic method of defining metadata descriptions directly.

Your idea to let Power BI inherit metadata from Power Query or allow dynamic descriptions is good.  Since this is not currently supported, I suggest submitting a feature request  on the Power BI Ideas Forum. This will bring attention to the need for this enhancement.

You can submit your idea here:  Microsoft Fabric Ideas.

If my response solved your query, please mark it as the Accepted solution to help others find it easily.  And if my answer was helpful, I'd really appreciate a 'Kudos'.

[rpiboy_1]

Submitted

[V-yubandi-msft]

Hi @rpiboy_1 ,

we wanted to check in as we haven't heard back from you. Did our solution work for you? If you need any more help, please don't hesitate to ask. Your feedback is very important to us. We hope to hear from you soon.

Thank You.

[dufoq3]

Update:

This is not possible. You can have a description i.e. as a separate column.

[rpiboy_1]

I.E. what? Where would I put a seperate column to describe the columns in the semantic model for the benifit of users building reports and performing analysis? The hope/goal was to update the information that is available for those users, and to avoid having to manually populate the data given the quantity of Columns involved and the fact that I already have the data structured to begin with.

[dufoq3]

You can import your Column Table into PowerBI as a separate table. So everyone can see the description.

[rpiboy_1]

That is terrible UX, not going to make user have to reference another table just to know/confirm what the data column represents. I'll deal with manual population of description, its just not ideal.

[dufoq3]

Hi, provide sample data and expected result please.

[rpiboy_1]

As requested, example data.
Data Table:

ID	Name	Value One	Value Two
1	Acme	78.33	53
2	Contoso	44.56	87
3	Zebra	22.47	39

Column Table

Internal.ID	Name	DataType	Description
ID	ID	number	unique identifier
obj-name	Name	text	name of object
obj-val1	Value One	decimal number	some description of this property
obj-val2	Value Two	number	some description of this property different from Value One

I already dynamically rename and properly type the columns in the Data Table from the Column Table. What I'd like to do is dynmically set the Column Description in the Data Table based on the Description Column of the Column Table. The List returned by Table.ColumnNames(#"Data Table") will match the list #"Column Table"[Name] since the name(s) used in Data Table originate from Column Table in the first place.

The question(s);

• Is the Description that is visible in Power BI a meta data value that can be set from Power Query?
• If it is what would a function start to look like to iterate over the columns in the Data Table and apply the meta data as defined in the Column Table

[V-yubandi-msft]

Hi @rpiboy_1 ,

Thank you for reaching out to the Microsoft Fabric Community with your query.  The description you see in Power BI is indeed metadata, but unfortunately, you cannot set it directly from Power Query. Power Query is primarily for data transformation and loading, while setting descriptions (metadata) for columns is done in the model layer of Power BI.

While you can't set the description directly in Power Query, you can prepare your data to make it easier to set the metadata manually or programmatically in Power BI.

Here's a general approach:

1. Manual Approach (For Small Models):
   • This method is suitable for smaller models where you can manually set the column descriptions in Power BI Desktop.
2. Store Metadata in a Separate Table:
   • Create a separate table in Power BI that contains the column names and descriptions.
   • Use this table to dynamically display descriptions in report tooltips.
3. If using Power BI Premium or PPU, you can update column descriptions programmatically.

If my response solved your query, please mark it as the Accepted solution to help others find it easily.  And if my answer was helpful, I'd really appreciate a 'Kudos'.