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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JChris
Helper II
Helper II

Generate new CSV daily, but keep (merge) old data into the new one

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.

 

9 REPLIES 9
Phil_Seamark
Employee
Employee

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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)


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.