Question on How and where source data is changed

[edcarden]

From what I've learned so far you can add the same source data file, an xlsx file, only once to PowerBI no matter if you are using workspaces. 

Assume that I load (using Power BI Desktop) the file Financial Sample.xlsx , which I have in a directory within OneDrive - Business,  and I use Transform to make some data changes and then next make a report.  Lastly I share the report so it's accessible in Power BI (The service). If I then within PowerBI The Service try to go to a different workspace and load that same xlsx file from the same location it say's I can't because it's already been uploaded elsewhere. My question is if I use that same Financial Sample.xlsx file for a Report in another project are the trasnformations I made intially when loading the file going to them be applied to any reports that use Financial Sample.xlsx as a source? Or are the changes/edits done via transform tied to teh report and not the Financial Sample.xlsx source file? 
When an XLSX file is Imported into PowertBI Desktop

[edcarden]

 Aditonal info on what I did might help. Using Powert BI Desktop I imported (but alaso transfomred) data from the samele file from Microsft, Financial Sample.xlsx .  I then created a report with multiple visualizatiosn, saved and then published. Next I wnet to teh power BI Service, and went to teh Workspace I created for this MS Following Along Training course and it prompted me to get data from the Financial Sample.xlsx which is  in my OneDriovbe loctaion where POwer BI Service can get to it. When I di this I was told the file already had been imporeted so my question is if I try and build a new report that is unrelated to the one I published but which uses the same Financial Sample.xlsx source file will the transformations made the first time be carried over to this report too?  I'm guessing that the transformations are never associated with the xlsx file but I've seen stranger things in Power BI so I don't assume anymore. 

[j_ocean]

"When I di this I was told the file already had been imporeted"

This is the bit that's confusing me, it shouldn't matter what else is looking at it. This leads me to beleive you did something that involves uploading excel to PBI which is a feature I've never used. That said, if the question is (and you'll excuse the paraphrasing) "how do I re-use these transforms I did here for other reports" then the answer is to do the transforms in a dataflow, in Service, vs PBI Desktop. Connect all your reports to this dataflow, only the dataflow connects to excel, and excel remains unchanging.

[edcarden]

In power BUI Desktop

1) Get Data ( an xlsx file stored in OneDirve Bussines ) but hit Transfomr on the Naviagtor screen (after select xlsx file) to made some changes to columns like date type, rounding, ect. 

2) Create Report

3) PUblish Report - to power bi service

Switch to Power BI Service

1) View existing publsihed rpeort

2) Go to a new/different workspace and hit Upload

3) Select the same xlsx file locate don OneDrive as outlined while working in Power BI Desktop - Get msg that "Workbook Already Exists". NOTE: That looking at what all is in teh workspace there is no instance of the xlsx file shown

I don't know of any simplert way to explain what I did and the msg I got doing it.  If I make a copy of the xlsx file I got data form while in Power BI Desktop and then go to the Power BI Service it WILL let me upload that copy of the file. 

So my question is since I can't uploade/import that same xlsx file again whill the transfomrations I made while in Powert BI Desktop carry over to any report that tries to use that Excel workbook as a source for the report?

[j_ocean]

I think I see whats going on, you don't need to upload the excel file at all. A pbix file connected to it should be able to reach back to One Drive when it refreshes. I'd guess you can't see a previous upload either because of a filter or a display bug, PBI Service can sometimes spaz out like that.

"whill the transfomrations I made while in Powert BI Desktop carry over to any report that tries to use that Excel workbook"

No, PBI Desktop reads only and (by default) saves a transformed copy of the data within itself. The source files remain unchanged. If you want that transformed version readily accessible to other reports you need them in a dataflow.

[edcarden]

The whole Power BI Training has been painful for a variety of reasons not the least being Microsoft’s constant changing of the UI and terminology making any documentation/training material quickly outdated combined with wat I feel is not well thought out information on the product.  

It make's sense to me why the original data source, whatever it is you are creating a dataset from, remains unchanged. What threw me was why I couldn't use the same xlsx file again in the Power BI Service in one of my Workspaces. I understand that a work space is just a way of organizing the various files used by Power BI but if I can't add that same xlsx file as a source foe a dataset to be used to power the report I'm creating in the Power BI Service then something's not right because I can't believe you have to create multiple copies of a data source like that just to create a new dat set.   

I am new to Power BI so maybe I still don’t have a grasp on everything but the way I understand it to work is that 

1) A data source (i.e flat file like XLSX or a RDBMS like SQL Server) is used to create a new dataset/dataflow that will live in Power Bi. The data source doesn't live there but the Dataset does; I believe this may also be called a semantic model. It’s not clear to me if a dataset, dataflow and semantic model are the same thing or different things. I know when I read anything Power BI related the terms dataset and dataflow seems to be used interchangeably 

2) After setting up a data source/dataflow and making whatever transformations are needed one then creates a report based on that dataset and possibly 1 or more other datasets. 

 3) Visualization are added to the report and they are powered by the data form the dataset/dataflow

 4) Upon completion of your Report optionally one may then create a dashboard which is made up of 1 or more visualizations from 1 or more reports. 

So after having done all of this, within the Workspace I've created this all in, I should have 1 dataset/dataflow, 1 report and 1 dashboard. If I look at the Workspace where I published the stuff from Power BI Desktop to I can see 1 Report file, 1 semantic model & 1 workbook.  If I switch workspaces and want to create a new report using the same datasource, that same xlsx file,  that was previously used in WPoer BI Desktop I'm assuming I have to select a published data set and then go find where that source xlsx file was initially shared or uploaded to yes?

I understand that the original data source is not itself uploaded just the data brought from it via the dataset , yes?

Thanks again

[j_ocean]

Again:

"you don't need to upload the excel file at all. A pbix file connected to it should be able to reach back to One Drive when it refreshes. [...] If you want that transformed version [of the source] readily accessible to other reports you need them in a dataflow."

A dataset <> dataflow. They both use Power Query but are handled differently in the workspace.

Dataset = semantic model.

" If I switch workspaces and want to create a new report using the same datasource, that same xlsx file,  that was previously used in WPoer BI Desktop I'm assuming I have to select a published data set and then go find where that source xlsx file was initially shared or uploaded to yes?"

No. Don't upload your excel file. You can make a new report off the existing dataset, or a new dataset off of a dataflow, or connect to the same file again in one drive.

[edcarden]

CORRECTION:

Thank you and I understand that the xlsx file itself is not uploaded but the data from it surely is else if you couldn't connect to the source with Power BI then anything built on it would be unusable.  If an xlsx file has 100 rows with 50 columns of data in each and I use that in Power BI I understand that the source XLSX file isn't itself uploaded but the data from it has to be. 

Let me elaborate on thsi b/c I seem to have doen a bad job conevying what I'm saying.  

Yes I understand that when you refresh the data sets, PBI must be able to access the source data,;an excel file in this example.  That said the report and Dashboards built on that data will change the numbers you see when applying filters and so on.  That data that tells the various visualiztaions what to show when user A has applied Filter X has to be stored somewhere. Does that make sense? 

[edcarden]

Thank you and I understand that the xlsx file itself is not uploaded but the data from it surely is else if you coudln't connect to the source with Power BI then anything built on it would be unusable.  If an xlsx file has 100 rows with 50 columns of data in each and I use that in Power BI I understand that the source XLSX file isn't itself uploaded but the data from it has to be. 

[chonchar]

@edcarden 
Hello,

I am going to take a stab at this, but someone should correct me if I'm wrong.

When you upload a report to Service for the first time, you won't get a notification. If you re-publish the same report, you'll get a warning "This will affect x amount of other reports" before you publish because service recognizes the data set, not the report. 

You could duplicate the xls file in SharePoint (I'm aware, not best practive), rename it, and then author another report from there. 

Best, 

Cam

*One Drive not SharePoint

[j_ocean]

You should be able to connect multiple PBI items, what are the steps you're using to connect?

In any event PBI doesn't write to excel so transforms should be on the copy within PBI's native data storage.