excel
42798 TopicsConvert PDF to Excel with Power Query: currency columns treated as text
When I import a PDF using Power Query the Amount column (e.g. £1.75) is interpreted as Text and I can't simply re-format it to Currency. My dumb workaround is to copy column data & paste it somewhere, find & replace "£" (with nothing) and cut & paste back where it came from. I guess there's a better solution? Thanks JezSolved119Views0likes6CommentsOrganisation Linked Data Type not working
I have a feature table published on Power BI Service that was previously accessible in Excel via the linked data type. However, for the past month, this connection has stopped working, and I'm unable to fetch data from the table. It appears the issue is related to authentication. Even the promoted dataset cannot be found or accessed anymore. This functionality was working fine earlier. I suspect this is due to a change in the authentication or security layer, because when I try to connect to a Power BI dataset in Excel using the "From Organization" option for a PivotTable, I am prompted to authenticate with Azure Active Directory every time. This suggests that persistent authentication is no longer being maintained. I believe this same issue is affecting the linked data type feature in Excel. Could you please clarify Has there been a recent change in the authentication model? Is there a workaround or fix to restore access to the linked data types from Power BI in Excel? Is the linked data type feature being deprecated? II tested on both current channel and beta channel If it's still working on your tenant, please do let me know or if its broken on your side as well , please update so that I can progress the issue accordingly. Thanks65Views0likes5CommentsPower Query is Missing Some Values on Import, Causing IDs to Map to the Wrong Records
Hi. I have a Power Query / Excel issue that I just cannot find a solution to, or a similar problem online. I have a third party cloud based database which I am querying into Excel via an OData connection. The source database contains some duplicate names. As this is real company data, I've created a little demo (screenshot below) to explain the problem. So say I had two companies called Bob's Buns in my original data set. Power Query does not pull the second instance of the name "Bob's Buns", which causes all the names below to line up with the wrong IDs. So now "12" is linked to "Clare's Cakes". You'd think I could just make sure that no two companies in my database have exactly the same name and this would fix the issue, but it doesn't. When I pull the data again after fixing the duplicates, it STILL results in this misalignment error. In fact if I renamed Bob's Buns (ID 11) to "Bob's Buns Bristol", and Bob's Buns (12) to "Bob's Buns Birmingham", Power Query still returns exactly the same result you can see under "What Power Query Returns" below. It's definitely the duplication causing the error, as there were a few instances of this within the database, and the same thing happened. I have done a static export to Excel from the source database, and the results line up correctly with their IDs. However, when I used OData & Power Query, I get this infuriating error. I hope I have explained myself OK! Help very much appreciated!16Views0likes1Comment264 nested IF statements -- alternative?
Apologies if this is a duplicate entry. I can't seem to find what I thought I posted earlier. I'm looking for alternatives to using LOOKUP when there are numerous nested IF statements. Please see attachment with a screen shot of part of my data. I have 264 individual tables of data based on specific characteristics (age, education, etc.). Each table has 2 columns: score X, score Y. I'd like to be able to pull the t-score (right column) based on the scaled score (left column) depending on the characteristics I select. For example, if A1 is someone's age and B2 is someone's years of education, etc, I'd like to pull the data from the table that matches those characteristics. If I had only a handful of tables, I could use something like: =IF(AND(A1<34,B2<9 [etc. for other characteristics]),LOOKUP(D2:E21),IF(AND(A1<40,B2<9 [etc. for other characteristics]),LOOKUP(H2:I21), .... however, I know this will not work because I have 264 different tables/combinations of characteristics. Appreciate any recommendations. Thank you!Solved153Views0likes8Commentsspell check anomaly
Had a mainly text spreadsheet, no formulae or cell notes, ran spellcheck and it came up with a word replacement suggestion. It found the word classifié (french for classified) I didn't remember using a french word. But when I tried to find the word in the sheet, it was not found. I tried a few different variations, got the same issue, could not find the word or even a derivation of the word (searched for class, for example) then I eventually erased all the text. Ran spellcheck again. Again it flagged the word as misspelled. There are no words in the spreadsheet though. So what is going on then? see attachmentSolved28Views0likes1CommentMaking Python-in-Excel worth it
I feel like Python-in-Excel has huge promise. Embedding a Seaborn correlation matrix in an analysis sheet with a couple lines of readable code? Keeping multiple dimensions of data in one cell efficiently? Making models that involve recursion and iteration without all the workarounds of LET/LAMBDA? Yes please!! I've messed around with it a lot but my experience has mostly been negative and from reading comments it sounds like a lot of others have had bad experiences too. A local version that was unlimited for free would be ideal of course. But I'm going to assume for a minute that isn't possible for business reasons. Most of my frustrations relate to these three things. So maybe if we could get these it would be worth it to use? A way to track usage and buy more credits so we don't get stuck with #BLOCKED! with no recourse Improve the xl() function so it works with variables inside it instead of just literals. e.g. foo = "B4" ; bar = xl(foo) currently doesn't work but it should. The error messages need to always tell you what line number the error is in I'm curious if other folks have tried it and agree or disagree with this wish list.49Views1like1CommentExcel SUM Function - Trying to Exclude Some Non-Contiguous Cells
I would like to Sum a column but exclude certain cells from that summing process, as they contain subtotals (so I do not want to add them twice). I have searched the web high and low and tried multiple formulas without success. The best I can get is a #VALUE! error. I know I've done this type of formula in the past with an earlier version of Excel, but cannot do it with this version (Office 10 Pro). Thanks.Solved2KViews0likes7CommentsFilter overwhelmed? Not showing all data
Hello! I've developed an application for my workplace, logging absences through the year on a backend spreadsheet and presenting the data on a frontend spreadsheet, with a filter available as managers requested the ability to review data based on names, dates, shift times, etc. It is a lot of data, there are 1833 absences logged over the last 2 weeks. The frontend filter continues to work as expected, and there is practically no delay in presenting the data. However, I've noticed that some of the columns are presented as blank. If I open the backend spreadsheet, suddenly the frontend filter shows all columns as populated. I'm not sure how to lessen the load on my filter, as I am assuming there's excess processing afoot. Any ideas worth a try, thank you! Filter code (crunchy, I know. Eek.) The date variables were my only idea to simplify the want to search from, to, and between dates depending on blank cells: " =IFERROR( LET( fromdate,('BACKEND.xlsm]RECORD'!E:E>=G7)*('BACKEND.xlsm]RECORD'!F:F>=G7), todate,('BACKEND.xlsm]RECORD'!E:E<=H7)*('BACKEND.xlsm]RECORD'!F:F<=H7), tweendate,(('BACKEND.xlsm]RECORD'!E:E>=G7)*('BACKEND.xlsm]RECORD'!E:E<=H7))+(('BACKEND.xlsm]RECORD'!F:F>=G7)*('BACKEND.xlsm]RECORD'!F:F<=H7)), blanks,FILTER('BACKEND.xlsm]RECORD'!A:L,('BACKEND.xlsm]RECORD'!B:B<>"")*('BACKEND.xlsm]RECORD'!B:B<>"Co-Ordinator Entering Leave:")*IF(ISBLANK(B3),1,'BACKEND.xlsm]RECORD'!B:B=B3)*IF(ISBLANK(C3),1,'BACKEND.xlsm]RECORD'!C:C=C3)*IF(LEN(E7)>0,'BACKEND.xlsm]RECORD'!D:D=E7,1)*IF(ISBLANK(I3),1,'BACKEND.xlsm]RECORD'!G:G=I3)*IF(ISBLANK(J3),1,'BACKEND.xlsm]RECORD'!J:J=J3)*IF(ISBLANK(K3),1,'BACKEND.xlsm]RECORD'!L:L=K3)*SWITCH(LEN(G7)+LEN(H7),0,1,10,tweendate,5,IF(LEN(G7)=0,todate,fromdate))), IF(blanks=0,"",blanks)),"No Results") " The data / filter results are listed below, of course personal details removed. Columns A, H, I, and K are all presented as blank until the BACKEND spreadsheet is opened (which defeats the purpose of this filter). BACKEND Data RECORDED Co-Ordinator Entering Leave: Employee Name: Date Called: Absence Start Date Absence End Date Shift Time Affected Actioned? Rostered? Reason for Leave Comments Covered by: TRUE John Smith Don Douglas 08/06/2025 09/06/2025 09/06/2025 0900-1730 NO Personal Leave (PL) Sick, recuperating from injury. Note pending. TRUE Jane Smith Deb Douglas 09/06/2025 09/06/2025 09/06/2025 0600-1430 Sick / Injured Did not feel well, Victoria moved to cover. Victoria Veck Jane Smith Doug Douglas 09/06/2025 09/06/2025 09/06/2025 0600-1430 Sick / Injured Victor Veck FILTER Result ENTERED Co-Ordinator Entering Leave: Employee Name: Date Called: Absence Start Date Absence End Date Shift Time Affected Actioned? Rostered? Reason for Leave Comments Covered by: John Smith Don Douglas 08/06/2025 09/06/2025 09/06/2025 0900-1730 Personal Leave (PL) Jane Smith Deb Douglas 09/06/2025 09/06/2025 09/06/2025 0600-1430 Sick / Injured Victoria Veck Jane Smith Doug Douglas 09/06/2025 09/06/2025 09/06/2025 0600-1430 Sick / Injured Victor Veck57Views0likes4Comments