Detect colored fonts in PowerQuery

[medmbchr1989]

Hi

I have a very large set of data (+1000 excel files, 100k lines each) in which i have some lines of data in red, the color differentiation is very important in my case and there is no columnin my data to do the differentiation. Is there a trick in powerquery that can detect colored font and give it a tag or a personalized column to differentiate it?

Also at this stage I take any tip that will  prevent me from manually processing each file for the colored font lines.

Thanks!

[v-nmadadi-msft]

Hi @medmbchr1989  

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-nmadadi-msft]

Hi @medmbchr1989 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-nmadadi-msft]

Hi @medmbchr1989    ,

We have not heard back from you if any of the answers has met your requirement kindly  Accept it as the solution  to help out other community members or get back to us with any other questions.
Thank you

[v-nmadadi-msft · Accepted solution]

Hi @medmbchr1989 

Unfortunately it is not possible to do the functionality you are looking for in power query.
https://answers.microsoft.com/en-us/msoffice/forum/all/preserve-text-formatting-with-query/c17723d7-...
If the requirement is to run Macros on files without opening the file try running VBscripts but expertise on the technique is beyond this forum.

Thanks and regards

[danextian]

hi @medmbchr1989 

Power Query can't do that. It can read the text but not the formatting so the differentiation as a text must exist in the data source.

[medmbchr1989]

@danextian the differentiation doesnt exist on the data unfortunately, i have more than a 1000 files each with 100k+ lines

[danextian]

If that is an excel file, you can write  macro to return the color in a separate column which is already outside the realm of this forum. Try this prompt in ChatGPT.

macro in excel to return the color of a text

[medmbchr1989]

As I said I have +1000 excel files, cannot apply a macro to each file separately unless there is a way to do so without opening the files.

[danextian]

A macro can loop through each file in a folder, opening each in the background so expect for a bit of sluggishness in the performance of your device,  but I would loop through them by batch.