Need to add a column from sharepoint excel file to power BI dataset

[akemr]

Hello all,

I have a power bi file connected to a sharepoint excel file and I already have a report of visuals ready to go. In the sharepoint file, I have a set of columns being used in the report say, from Column A to Column Z.

I need to add a new column in the middle of this set of columns, say Column Q, and I need to use Column Q's data in the BI report. 

I've tried adding the column in the sharepoint file, and then refreshing the local dataset. Due to the many editing steps i've made, the local dataset does not load properly. 

I went through the task of making the changes from the top, adding the new column Q, and making sure the consecutive editing steps are not Errors. I finally managed to finish it but on hitting "close and apply", the dataset reorders the columns on its own and the report has no visuals. 

Any idea on what I could do to fix this issue? Or an easier way to do this?

[amitchandak]

@akemr , Not very clear. I am assuming you went to transform data/edit query, right click on table and open advance editor and made sure columns are added there in the order you wanted. Most probably in first 2-3 steps

[Fowmy]

@akemr 

The steps you took to check the query steps are fine. Can you share your query to see if the flow of steps breaks or misses on the way?

You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[akemr]

HI @Fowmy ,

Sure but i dont think its necessary now as i managed to create a new query and just repeated the steps there instead. I have a new issue now.

In the power query editor, my rows are numbered properly from 1 - 109. In my sharepoint excel, after Row 109 there is a Totals row, which I have excluded from my BI dataset. It does not show up in the power query editor.

However, when I "close and apply", a few rows from the top are reordered to the bottom and the Totals row appears from nowhere. I dont need the Totals row as it interferes with my visuals. Any ideas??

The Totals row is right above Row 16

[Fowmy]

@akemr 

Great!
Power Query shows a preview of the data, you will not see all the records from the dataset. If you filter out those rows that are not necessary, it should not appear in the data model.

Hope you applied a correct filter in PQ to exclude those rows. 

I did get what you mentioned as "I have excluded from my BI dataset"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[akemr]

What I meant by that is I have done a step in PQ to remove the last few rows from the sharepoint excel. As a result, the Totals row is removed from the bottom of the excel, and Row 109 is the last row. Only when I close and apply, the rows get reordered and Totals row comes back.

[akemr]

Okay so I just tried reopening the power BI file and going through the dataset again. Turns out I hadn't actually deleted the Totals row after all hahaha. Silly mistake on my part, but the reordering of rows is still occurring even after I remove the Totals Row. It doesn't really affect the visuals/report, but it's just a bit annoying to see. Any ideas how to get past it? 

[Fowmy]

@akemr 

I am not sure what other steps are taking place in your query, to retain the order, you need to apply the following step within your query

BufferedSource = Table.Buffer(Previous Step)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn