Different ranges of data uploads

[Mariya_Rys]

Hello!

I’m form Kazakhstan, BI beginner. I’m using an uploaded data from several xls daily technological geo journals, created by geologists. Every morning these journals are uploaded to OneDrive and BI report updates data.

When first uploading through “Get data”, Desktop Navigator offered a range of 23 columns. The journal consists of 16 columns, all empty columns #17-23 were deleted at the stage of “Edit query”. BI report was finalized, published and worked for months.

Last update was failed with the following error – “couldn’t find the column23”.

Xls source report has this column empty. I changed the Edit query sequence and kept the coulmn23 in BI report regardless its empty and I don’t need it. The same error popped up with all the empty columns #17-23. I removed the step of deleting empty columns and report started working, but now I have 7 useless empty columns in the report.

Is there any possibility to select the range of data within first uploading before editing it?

[vivran22]

Hi,

Has this been resolved? If yes, then please accept the appropriate answer as solution otherwise please share the sample data set for further assistance.

Regards,
Vivek

[Mariya_Rys]

---

@vivran22 wrote:
Hi,

Has this been resolved? If yes, then please accept the appropriate answer as solution otherwise please share the sample data set for further assistance.

Regards,
Vivek

---

Hi, Vivek,
No, unfortunately it didn’t resolve my issue as I need to remove the empty columns I don’t need one step prior.
E. g. if I upload the excel table with 3 columns, and navigation pane offers me 10 columns to load or edit, I can’t remove these 7 unnecessary columns in Power Query advance editor. As I risk that after some period, within the data scheduled update, the Power BI identify not 10 but 9 columns and notify me about update error due to impossibility to find 10th column.
I guess this happens because that table initial owners make some temporary calculations aside the table, add or remove coolants. Those changes do not affect the table I need, but unfortunately affect the recognizable uploading range for BI.

[vivran22]

@Mariya_Rys 

Hi,

Welcome to the BI club 🙂

You may try "Advanced Editor" in the Power Query (when you are transforming data). In that under #Changed Type you will find list of columns the Power Query is importing. Remove all those which you do not need.

For example:

#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Row ID", Int64.Type}, {"Order ID", Int64.Type}, {"Order Date", type datetime}, {"Order Priority", type text}, {"Order Quantity", Int64.Type}, {"Sales", type number}}

If I do not need Row ID column, then I will remove "{"Row ID", Int64.Type}, ".

Hope this will help.

Thanks,

Vivek

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mariya_Rys]

Hi Vivek,

Thank you for your quick response! 

[v-yuta-msft]

@Mariya_Rys ,

"Is there any possibility to select the range of data within first uploading before editing it?"

Currently there's no such feature in the navigator pane. Something should be confirmed, your data source is in local onedrive file in your PC/Server and you snyc the onedrive file with onedrive online, right? The blank column is valid in power bi report so please check if the local xls file has been modified, especially the blank value columns(Sometimes blank cells also contains invalid characters in xls file).

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mariya_Rys]

---

@v-yuta-msft wrote:

@Mariya_Rys ,

"Is there any possibility to select the range of data within first uploading before editing it?"

Currently there's no such feature in the navigator pane. Something should be confirmed, your data source is in local onedrive file in your PC/Server and you snyc the onedrive file with onedrive online, right? The blank column is valid in power bi report so please check if the local xls file has been modified, especially the blank value columns(Sometimes blank cells also contains invalid characters in xls file).

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

---

Hi, Jimmy Tao

Thank you for your response, yes, I suspect this happens because that table initial owners make some temporary calculations aside the table, add or remove columns. Those changes do not affect the table I need, but unfortunately affect the recognizable uploading range for BI.