Home

Need help in importing data from web. Conditional formatting not working on imported data

%3CLINGO-SUB%20id%3D%22lingo-sub-856135%22%20slang%3D%22en-US%22%3ENeed%20help%20in%20importing%20data%20from%20web.%20Conditional%20formatting%20not%20working%20on%20imported%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-856135%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20importing%20data%20from%20this%20link%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.nseindia.com%2Flive_market%2FdynaContent%2Flive_watch%2Foption_chain%2FoptionKeys.jsp%3FsymbolCode%3D-10006%26amp%3Bsymbol%3DNIFTY%26amp%3Bsymbol%3DNIFTY%26amp%3Binstrument%3D-%26amp%3Bdate%3D-%26amp%3BsegmentLink%3D17%26amp%3BsymbolCount%3D2%26amp%3BsegmentLink%3D17%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.nseindia.com%2Flive_market%2FdynaContent%2Flive_watch%2Foption_chain%2FoptionKeys.jsp%3FsymbolCode%3D-10006%26amp%3Bsymbol%3DNIFTY%26amp%3Bsymbol%3DNIFTY%26amp%3Binstrument%3D-%26amp%3Bdate%3D-%26amp%3BsegmentLink%3D17%26amp%3BsymbolCount%3D2%26amp%3BsegmentLink%3D17%3C%2FA%3E%3C%2FP%3E%3CP%3EAfter%20loading%20of%20the%20table%2C%20I%20am%20applying%20conditional%20formatting%20to%20get%20data%20bars.%20But%20I%20am%20getting%20data%20bars%20in%20only%20one%20column%20(Column%20named%20Strike%20pice).%20In%20all%20the%20other%20columns%20the%20formatting%20is%20not%20working.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F131853iA5727835E62FAB5F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22da.jpg%22%20title%3D%22da.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-856135%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-856146%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20in%20importing%20data%20from%20web.%20Conditional%20formatting%20not%20working%20on%20imported%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-856146%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F409152%22%20target%3D%22_blank%22%3E%40chandragoku%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20since%20all%20such%20columns%20most%20probably%20are%20formatted%20as%20text.%20If%20you%20replace%20all%20%22-%22%20on%20nothing%20or%20zero%2C%20and%20convert%20the%20rest%20to%20numbers%2C%20conditional%20formatting%20shall%20work%20on%20columns%20with%20numbers.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-856147%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20in%20importing%20data%20from%20web.%20Conditional%20formatting%20not%20working%20on%20imported%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-856147%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F409152%22%20target%3D%22_blank%22%3E%40chandragoku%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheck%20the%20datatype%20of%20other%20columns.%20They%20seem%20to%20have%20Text%20datatype.%20Edit%20your%20query%20and%20change%20the%20datatype%20to%20numeric%20datatype%20i.e.%20Whole%20Number%20or%20Decimals%20and%20it%20should%20word%20as%20expected.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-856155%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20in%20importing%20data%20from%20web.%20Conditional%20formatting%20not%20working%20on%20imported%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-856155%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETried%20it.%20But%20the%20problem%20still%20persists.%20This%20was%20not%20the%20problem%20in%20office%202010%20%26amp%3B%202013.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-856157%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20in%20importing%20data%20from%20web.%20Conditional%20formatting%20not%20working%20on%20imported%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-856157%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETried%20it.%20But%20the%20problem%20still%20persists.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-856159%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20in%20importing%20data%20from%20web.%20Conditional%20formatting%20not%20working%20on%20imported%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-856159%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F409152%22%20target%3D%22_blank%22%3E%40chandragoku%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20see%20on%20you%20screenshort%20that%20these%20columns%20are%20formatted%20as%20text.%20You%20tried%20to%20convert%20to%20numbers%20and%20still%20have%20texts%20or%20you%20tried%20something%20else%3F%3C%2FP%3E%0A%3CP%3En%202010%20perhaps%20similar%20query%20returned%20numbers%2C%20not%20text.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-856162%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20in%20importing%20data%20from%20web.%20Conditional%20formatting%20not%20working%20on%20imported%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-856162%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%2C%20if%20I%20am%20formatting%20the%20data%20into%20number%20after%20loading%20the%20table%20then%20it%20is%20not%20working.%20Albeit%2C%20if%20I%20am%20formatting%20it%20in%20power%20query%20editor%20before%20loading%20it%20on%20spreadsheet%20then%20it%20is%20working.%20Seems%20like%20a%20solution%20was%20found.%20Thanks%20alot.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-856165%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20in%20importing%20data%20from%20web.%20Conditional%20formatting%20not%20working%20on%20imported%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-856165%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F409152%22%20target%3D%22_blank%22%3E%40chandragoku%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20tried%20that%20with%20legacy%20Web%20connector%20which%20returns%20numbers%2C%20not%20texts.%20Data%20bar%20conditional%20formatting%20works%20on%20returned%20result.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20116px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F131858i625EDEADB57D56D6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20use%20Power%20Query%20by%20default%20it%20converts%20these%20columns%20to%20text.%20If%20not%20Load%20but%20Transform%20and%20add%20couple%20of%20more%20steps%20-%20apply%20Decimal%20Number%20type%20to%20these%20columns%20and%20Replace%20all%20errors%20on%20nulls%2C%20it%20works%20as%20well.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20149px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F131859iF5A9B9868C3237A9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-856202%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20in%20importing%20data%20from%20web.%20Conditional%20formatting%20not%20working%20on%20imported%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-856202%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F409152%22%20target%3D%22_blank%22%3E%40chandragoku%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20in%20Excel%20just%20format%20cell%20from%20text%20to%20number%2C%20the%20value%20of%20the%20cell%20won't%20be%20converted%20from%20text%20to%20number%20even%20if%20text%20has%20number%20representation.%20You%20need%20to%20re-enter%20value%2C%20or%20use%20VALUE()%2F--%2C%20or%20some%20other%20methods%20to%20convert%2C%20depends%20on%20situation.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-856217%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20in%20importing%20data%20from%20web.%20Conditional%20formatting%20not%20working%20on%20imported%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-856217%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F409152%22%20target%3D%22_blank%22%3E%40chandragoku%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20what%20I%20suggested%20and%20maybe%20you%20didn't%20pay%20attention%20to%20what%20I%20was%20suggesting.%3C%2FP%3E%3C%2FLINGO-BODY%3E
chandragoku
New Contributor

I am importing data from this link - 

https://www.nseindia.com/live_market/dynaContent/live_watch/option_chain/optionKeys.jsp?symbolCode=-...

After loading of the table, I am applying conditional formatting to get data bars. But I am getting data bars in only one column (Column named Strike pice). In all the other columns the formatting is not working.

da.jpg

 

9 Replies

@chandragoku 

That's since all such columns most probably are formatted as text. If you replace all "-" on nothing or zero, and convert the rest to numbers, conditional formatting shall work on columns with numbers.

@chandragoku 

Check the datatype of other columns. They seem to have Text datatype. Edit your query and change the datatype to numeric datatype i.e. Whole Number or Decimals and it should word as expected.

@Sergei Baklan 

Tried it. But the problem still persists. This was not the problem in office 2010 & 2013.

@chandragoku 

I see on you screenshort that these columns are formatted as text. You tried to convert to numbers and still have texts or you tried something else?

n 2010 perhaps similar query returned numbers, not text. 

@Sergei Baklan 

 

Well, if I am formatting the data into number after loading the table then it is not working. Albeit, if I am formatting it in power query editor before loading it on spreadsheet then it is working. Seems like a solution was found. Thanks alot.

@chandragoku 

I tried that with legacy Web connector which returns numbers, not texts. Data bar conditional formatting works on returned result.

image.png

If use Power Query by default it converts these columns to text. If not Load but Transform and add couple of more steps - apply Decimal Number type to these columns and Replace all errors on nulls, it works as well.

image.png

@chandragoku 

If you in Excel just format cell from text to number, the value of the cell won't be converted from text to number even if text has number representation. You need to re-enter value, or use VALUE()/--, or some other methods to convert, depends on situation.

@chandragoku 

That's what I suggested and maybe you didn't pay attention to what I was suggesting.

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies