cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
elbachaoh
Frequent Visitor

Rename Columns - SQL Direct Query Datasource - Using Dynamic M query parameters

I'm using a direct query to an SQL data source

= Sql.Database("localhost", "DBName", [Query=Text.Combine({"Select *From [DBName].[dbo].[",TimeData,"]"})])

 

TimeData is a Dynamic M query Parameter that referes to different tables in the database.

For example if TimeData refers to "Table1" in the databse, I'd expect to see columns "col1, col2, col3,col4"; which is the case. However, different tables in the database might contain different columns, for example "Table2" might have "col1,col2,col5"; but when I change the parameter TimeData from Table1 to Table2 I still see "col1, col2, col3,col4" in the preview and on the dashboard instead of "col1,col2,col5"

 

How can I change column names dynamically when I change the parameter for the query?

 

Thanks in advance!

@amitchandak @SpartaBI @tamerj1 @Vijay_A_Verma 

 

5 REPLIES 5
elbachaoh
Frequent Visitor

@lbendlin I do not wish to remove the additional columns, on the contrary I want them to appear in my data model.

Any thoughts?

 

Thanks

That is not a sustainable approach. Think about unpivoting instead, it is much more manageable.

elbachaoh
Frequent Visitor

Ibendlin, thanks for you response;

 

To answer your questions:

The next step is to visualize them.

These columns would go on "line chart" visuals. Columns names are mostly identical across all tables but there are certain tables with additional columns.

 

Additional Explanation:

Columns that exist in all tables will have the same name, but certain tables might have additional columns.

From my example above, "col1, col2" are shared between  "Table1" and "Table2"; but I'd expect to see a change when shifting between the two tables: PowerBi to make available "col3,col4" for "Table1" and "col5" for "Table2".

To put things in proper context, there's hundreds of tables of similar structures having around 50 columns with same names, and difference is usually in 1-5 extra columns. That's why I am using parametrized queries because it covers the similarities but I cannot see the change for the these few additional columns.

 

Please let me know if you need additional info

Two options

 

a)  create a ETL with ALL potentially appearing column names and then use "Handle missing columns"  functions in Power Query (there are plenty of these)

b) Identify the main (immutable, header level columns) in your data source and then unpivot all other columns. That way your problem goes away and the visualization becomes much easier too.

lbendlin
Super User
Super User

What would be your next step? How are you planning to visualize your data if the column names are dynamic?

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.