Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table with columns Software, Vendor and ID that gets updated daily on PBI. I created a temporary Report for it so I can diplay this table using the Table view and then export it to CSV, this file is named "software-assessment.csv". I need to add a new column "Status" to this CSV. A line from it would be like that:
Software,Vendor,ID
Office 365,Microsoft,office 365:microsoft
Firefox 50,Mozilla,mozilla:firefox 50
Super Software,"",super software:
Photoshop CC,Adobe,photoshop cc:adobe
Above is the CSV as it got out from PBI, as soon as I clicked the "export data", then I proceed to do my manual job below:
Software,Vendor,ID,Status Office 365,Microsoft,office 365:microsoft,0 Firefox 52,Mozilla,mozilla:firefox 50,2 Random Game,"",super software:,1 Photoshop CC,Adobe,photoshop cc:adobe,0
The last column in that CSV, "Status", is the one I'm inputing manually, including the header. I may need to do this on a daily basis while merging the old data with the new data. So, tomorrow there might be a new software, "Another Game,GameCompany,another game:gamecompany", and I want this line to be inside "software-assessment.csv" while I keep the rest intact. Something like that:
Software,Vendor,ID,Status Office 365,Microsoft,office 365:microsoft,0 Firefox 52,Mozilla,mozilla:firefox 50,2 Random Game,"",super software:,1 Photoshop CC,Adobe,photoshop cc:adobe,0
Another Game,GameCompany,another game:gamecompany
Then I would simply add the status for this new software. How can I achieve that?
PS: This is not my real data, but this case is kinda similar. I hope I gave the right idea.
Hi @JChris
What determines the rule for the Status column? Is this something that can be conversted to a formula so you don't have to manually add it?
I have to manually assess it, there's no furmula.
You can use the Append feature in Query Editor along with Remove Duplicate rows to possibly achieve what you are after (I think)
How would that help me? I believe this isn't really suited for my case.
Once you create your"software-assessment.csv" do you then bring that back into Power BI as a new datasource? I thought this dataset could be merged with the source you get your new rows for, meaning you won't need to maintain rows you have already applied a status to.
Yes, I would use my "software-assessment.csv" as a new source in PBI. Can you explain more about this "merge with the source"? I didn't really get the idea here, sorry.
Where does your original data come from? In your post you walk though an example of a new item appearing. Where does that come from?
There's a hourly dump of the softwares installed on the machines into a file in the network (\\PATH\source.csv). This is my source, which I named query "Softwares" in PBI. I create another query using "Softwares" as reference and then I generate the ID using the software name and vendor, and removed dups, this new query is called "Softwares Unique". I ignore versions here, I just care about name and vendor.
This is where "Another Game" comes from, from the file "\\PATH\source.csv", which goes into "Softwares" which goes into "Softwares Unique" which I open in Reports so I can do the "export data" into the software-assessment.csv.
Hi @JChris,
To append queries in Power BI desktop, you can take a look at this blog: Append vs. Merge in Power BI and Power Query.
In my opinion, assume the source.csv contains columns Software,Vendor,ID,Status, each time add new record in the source.csv, refresh the report will have updated data. Then you can export to .csv get orignal data and added records.
Best Regards,
Qiuyun Yu
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |