data layer for re-usability, data source template

[hgauhar]

hi everyone

i will really appreciate if you can help with my problem.

I have built some reports using a csv file with 4 base columns . lets call them A,B,C,D

now i have a different clients and there will be additional columns in same table for different clients.

for example for client XYZ i have csv file with base columns plus some additional columns:A,B,C,D,E,F,G,H

I have another client MNO who has those same base columns and some additional ones: A,B,C,D,E,R,T,H

Please keep in mind that i only intend to use column A,B,C,D  and ignore others.

Now it would be ideal if i could use the same dashboard i built using my  base columns A,B,C,D for these two clients. It will reduce my work significantly.

Is there a way i can create a query or a middle data interface layer to upload csv files with different column names (containing 4 BASE COLUMNS)

i have seen that POWER BI doesnt recognize column names in csv files. it throws off structure of csv file as soon as someone enters additional columns in it.

[v-juanli-msft]

Hi @hgauhar 

Is any of the above answers helpful?

Do you have many csv files with "A,B,C,D" columns and other columns?

but you only want to hold "A',B,C,D" columns in Power BI Data model and use then in the visuals, right?

Could you show sample data structure of these csv files?

Best Regards

Maggie

[amitchandak]

@hgauhar , you can try this too.

Go to edit transformation. Right click on the table, and go to advance editor see if see the column name. You can reduce it to A,B,C,D. But these four should be the first 4 columns.

But I would suggest to explore option suggested by @parry2k 

[hgauhar]

thanks for responding.

I want to automate this process. Ideally, i want my client to specify the path of file with these columns A,B,C,D ,E,F,G etc 

and in the advanced editor i only pick 4 columns from my client's file not 7

[parry2k]

@hgauhar you can surely combine multiple csv files  wtih different column names. after file is read, your next step should be select columns a/b/c/d and it is automatically going to remove other columns regardless what those columns are

here are few posts on how you can combines files:

https://powerpivotpro.com/2016/12/import-csv-files-folder-filenames-power-bi/

https://sharepointmike.wordpress.com/2016/03/19/loading-and-combining-multiple-csv-files-in-power-bi...

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[hgauhar]

thanks for your comment.

i tried using both methods but i am not able to create a template based approach.

whenever i try another data export with a set of new columns.. the structure is thrown off and i get junk data in reports

See the query below, is there a way to tell power bi that the number of columns can be variable or dynamic however we only want to pick first 4 columns and ignore the others.. i tried the choose columns approach and there is bug in it!... i am gettinn wrong data in my base 4 columns

= (Parameter1) => let
Source = Csv.Document(Parameter1,[Delimiter=",Encoding=1252, Columns=4, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers"