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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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