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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Mariya_Rys
Regular Visitor

Different ranges of data uploads

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?

6 REPLIES 6
vivran22
Community Champion
Community Champion

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


@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
Community Champion
Community Champion

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

Hi Vivek,

Thank you for your quick response! 

v-yuta-msft
Community Support
Community Support

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


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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.