data source number of columns change

[Anonymous]

Hello,

I am using a data source on my reports that is dynamic in the sense that the number of columns may change.

Is there a way to capture this in the query and resolve it programatically?

Thank you

J

[PattemManohar]

@Anonymous  Could you please make it more clear and particular by giving an example. 

I understand (For example), that you have a table in the database and the number of columns in the table might change (increase or decrease) and you want to handle that without failing the visuals that are using that dynamic table. Is that correct ?

[Anonymous]

Taht's right, @PattemManohar .

For instance, let's say that my most frequent data source is a table like the one below.

But sometimes, Column 3 does not exist. It is not a datasource error, it just happens that it is dynamic in that sense.

I would like to build my query so that if Column 3 exists it then processes its values but so that it does not break if Column 3 does not exist and moves on to process the existing columns.

Does this make sense?

Thank you for your help.

J

Column 1	Column 2	Column 3
ClashID	Level	Grid Location
1	GF	A1
2	First	B2
3	Second	B1
4	Third	B1

[PattemManohar]

@Anonymous In that case, try to create a View on top the table to have the column names as C0, C1, C2 etc... based on the number of columns. So you are not dependant on the actual names and the view should take care of that logic. In PBI, you always use the columns that will have same names i.e. C0,C1,C2....etc.. and then you can do your steps on top of it...

[Anonymous]

Apologies, I am not sure if I follow what you mean.

"try to create a View on top the table to have the column names as C0, C1, C2"

Another question is that if it happens that, in my example, it is Column 2 that is missing then, C3 will become C2 and will that not mess the query in how to deal with the values as they will need to be handled differently?

Thank you

[PattemManohar]

@Anonymous Not sure what exactly you want to achieve at the end (or all the transformations that you want to do on top of that dataset). I suggest to have all the column types as varchar, you need to try to capture the number of fields from the Metadata tables of the database. 

Example, your table is has sno,name,address. Your view will contain C0,C1,C2. If your table structure changes then your view should contain only C0,C1... (Every time you drop and recreate the view script automatically or create a procedure to populate a table).... Just a very high level suggestion..... Then you will be using the select * from viewname (irrespective of the structure changes it will show the latest structure)