Forum Discussion
Collecting cell format data into Power BI
Hello,
My team and I work with a bunch of client records that we update. In the spreadsheet that we track records with any fields that need to be changed in the metadata for a record are colored red. It's this red coloring that I need to collect into PowerBI. The client has changed some of their processes and we have a new column that we have to fill in to also indicate these changes. So I need to build a report that checks if a cell is red then has the information been added to the new column. As well as the reverse. We deal with thousands of records and we use Power BI to help us QC our work. This is now a new QC step that we need to include.
FYI the client uses Smartsheets instead of excel for record tracking, but would be good to know how to do this with any imported spreadsheet data.
Thank you!
6 Replies
bhenson Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Hi bhenson,
What type of data source are you working on? If you mean excel sheet, current power bi only gets the data, these additional styles and scripts on the worksheet will not be imported at the same time.
In addition, did these change operations apply to add new records to your tables or directly modify/replace the original records? If you mean the second one, current power bi not able to trace, it does not include any historical analysis features.
You need to back up these records to other data sources, then you can use powerbi to get data from both two data sources and compare.
Regards,
Xiaoxin Sheng
Hi Anonymous
For this client we are using Smartsheets instead of excel since both us and the customer can access the data without company constrant issues.
As for the new process the client wants us to duplicate the information that we have been marking in red into a new column. I am aware that PowerBI is unable to collect data changes, thus I want to use PowerBI to check that the information that we've marked in red has been included in the new column. The example below would result in PowerBI concluding that the Address is missing form the Meta-data updates column since the text is red but not included.
EX:
Address City Project Number Project Date Meta-Data updates 1234 w Main st Candyland ABC12356 1/1/2022 change city to Candyland, change project date to 1/1/2022 The team is in the habbit of marking cells that they change information in to red, but not so much for filling in the new column. As best as we try we are human to where mistakes can be made so it would be nice to use PowerBI to help us check that we didn't miss anything.
As for getting a record of what things were changed when, only the client has access to run that report which we receive at the begining of the month for the previous month. We need something that we can use to check for issues ideally same day.
Please let me know if there is anyway to do this check in PowerBI.
Hi bhenson,
In fact, power bi only gets the raw data to the power bi side. Normally they do not include any style and cell format information.
For this scenario, you need to also get the data from the table which stored the customize style or highlight information. Then you can use these information to check and remark original table records.
Regards,
Xiaoxin Sheng
