Forum Discussion

elbachaoh's avatar
elbachaoh
Frequent Visitor
4 years ago

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...
Need Help
elbachaoh's avatar
elbachaoh
Frequent Visitor
4 years ago

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's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    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.