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

[elbachaoh]

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 

[elbachaoh]

@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

[lbendlin]

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

[elbachaoh]

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

[lbendlin]

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]

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