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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
hgauhar
Regular Visitor

data layer for re-usability, data source template

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.

 

 

5 REPLIES 5
v-juanli-msft
Community Support
Community Support

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
Super User
Super User

@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 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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
Super User
Super User

@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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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"

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.