Importing strike through and font colours from excel to table on Power BI

[Anonymous]

Hi all,

I am getting data from an excel sheet which has values that has strikethrough and have red fonts. 

Is there any way I can have them show up the same way in the table visualisation as well?

As I would like to link data from that excel to another another excel to get a table and export it out into an excel sheet.

Thanks

[pranit828]

HI @Anonymous 

When we get the data from any source the engine get only ther data not the formatting. So, after getting the data source, you can take steps on data.

This not yet supported on PBI, you can submit your idea

https://ideas.powerbi.com/forums/265200-power-bi-ideas

[Greg_Deckler]

@Anonymous - I am not aware of a way to do that, you could do the red font but not the strike through. Conditional formatting.

[Anonymous]

@Greg_Deckler

how can I do that on the data that I am extracting from the excel with rows in red? 

[Greg_Deckler]

@Anonymous - Well, I guess I was assuming that there was some reason or pattern as to why those things are in red or are you saying that some human is just flagging them as red for some reason that is not discernible as a pattern to a computer?

[Anonymous]

Hi @Greg_Deckler

Some human is actually formatting it in red and placing a strikethrough on the different rows.

Is there a way for me to do that?

Thanks

[Greg_Deckler]

@amelynatwork - I really do not think so. https://docs.microsoft.com/en-us/powerquery-m/excel-workbook just returns data, not any formatting information.

The only possible thing I could think of is if you could write some kind of R or Python script to access the Office API's or something but I think that is a very, very long shot.

@ImkeF and @edhans you are both much more excellent at Power Query than I, any ideas?

[edhans]

You absolutely cannot return formatting in Power Query. There is actually a bug related to XLS files (not XLSX) that weird formatting can trick the Power Query importer. I opened a support ticket and discussed the possibility of retrieving the formatting to make the numbers in my article show up properly as negative. The Power Query team said it couldn't be done, and they weren't going to fix the bug - which I 100% understand given the XLS format was replaced in 2007.

Either use VBA, use some Excel formulas to add new columns of data with info that Power Query can use. I am not sure if you can write a formula in Excel to look at that kind of formatting. That would be a question for the Excel forums.

[ImkeF · Accepted solution]

Not sure my understanding of the requirement is correct here, but you can retrieve formatting information from Excel-files with some fairly extensive scripting: https://github.com/ImkeF/Power-Query-Excel-Formats

But I don't see a way to get this information outputted through Power Query in a useful way. So VBA would probably be a better way here.

[Anonymous]

Hi there,

how can I do that on the data that I am extracting from the excel with rows in red?