PowerQuery not reflecting changes to filenames in Sharepoint Library
Hi All. I've got a simple SPO library with files that I need to query within Excel via PowerQuery, I'm using the Query Type SharePoint List rather than SharePoint Folder because I need other metadata from the library. So far so good, query works great, now the issue, IF I change the filename in the library it does not update in the PowerQuery results. New files will appear OK, other metadata changes appear Version numbers update. Simply the filename will not show the change, Cleared the PowerQuery Cache, that didn't work. File in Library Result in PowerQuery If I create a new Query in a fresh Spreadsheet, same result? Any suggestions greatly appreciated. Cheers RussAdding data ONLY when two cells match across sheets...
Hi all, Apologies: this is complicated... I'm working across x2 sheets in an Excel book. On sheet 2, I have my export page, where I paste the uploaded data for it to fill in sheet 1. On sheet 1, I have the historical data which I need to add to weekly. Sheet 2: Export Page including current formula (based on a working formula on another sheet we use. I need both forename and surname to match on the next page, and automatically paste columns C and D if/when/where they do, then tell me where any names have been missed (new students, etc.) so I can add them to the doc. So if A2 and B2 were on A6 and B6 on sheet 1, that is where the data (C2 and D2) would be pasted, to ensure that the student retains their own points balances. Sheet 1: Overview Page - I intend on the above columns C and D then being put into columns J and K on this page. I'll then just copy and paste the data across into the correct week following analysis, ready for the formula to re-populate those cells with the coming week's data. I switched forename and surname around for readability but can return if that makes it easier (for GDPR purposes, I have removed all names). The formula there had green "Yes"s through the page when I used the original data. When I have gone to update today, everything is a red "No", as you can see, so I clearly don't have the right formulas!! Appreciate your help, as I've been working on this for a couple of weeks now and just can't figure it out!! The formula on the other sheet that I was trying to work from was: =IF(COUNTIF(Table1[@[Name and tutor]],A2)=1,"Y","N")
Visual and dax level optimization that causes report slowdown
Hi, I have this visual as attached, that has lot of visual level filters applied it has a dax measure called data completeness as below Data Completeness = var _total = COUNT('Calendar'[Date])*COUNT(Points[DBName-Point_Id]) var _result = [Count of Exisitng Days]/_total return _result It references a dax called Count of Exisitng Days as below Count of Exisitng Days = Var dates = SUMMARIZE(Data, Data[DBName-Point_Id], Data[Date]) Var Ext_dates = COUNTROWS(dates) return Ext_dates Now, the problem I have here is the performance of this visual is causing report to slow down. Please let me know how to optimize the dax and further steps to increase the performance PFA file here PR-419 - Data Coverage - Copy.pbix Thanks in advance! SergeiBaklan
Data extraction from Pivot
Dear Experts, Greetings! I have a data like below(Worksheet attached too):- Column A(having rntis), B (having slots) and C having the transmission(DL_1_x) Out of this , for each rnti , I need to populate like in the Result, ( I gave 2 examples for rnti's 13648 11808 So, for rnti 13648, transmissions are low in slots 3 & 10, so put "x" in the slots 3 and 10, Similarly , for rnti - 11808, we have less transmissions in slots 0/8/18 so in below Result we see "x" in the corresponding slots. Can you please share some Logic/formula to achieve this? Also, apart from x, if we can also have some data like how much %age loss per rnti per slot for each slots ? Thanks in Advance, Br, AnupamPower Query Question
Dear Experts, Greetings! I have a data like below In column A, we have different Organizations, and in B, I want like if A contains "L3 SW" or "CP Integration", then put L3 SW, but in Conditional column in Else IF, only one criteria can be choosen as below:- Can you please share how and which format shall be used so that "or" can be used in same condition as below? "," doesn't help seems Thanks in Advance, Br, AnupamSOLVED - Hyperlink function works as Flash Fill but not as Dynamic/spill array
Hello all, I have tracker logs that I'm comparing to my bank and have solved all the issues so far, except for how to spill my Find & Go To hyperlink function. I have a complex formula in range $I$3 that will list all mismatches (bank transactions that are not in tracker and tracker logs that are not in bank) via spill and a data validation list in range $J$3:$J$1000 to select an action from. In range $K$3, I need a spill formula that will search the import sheets for the amount shown on the mismatch list, for each mismatch, then go to it upon click. In range $K$3:$K$1000, I currently have individual formulas that will check if the mismatch contains "Bank" and if so will display a hyperlink that extracts all text then reads the amount as a value, then matches it to the Tracker Import sheet. Otherwise, it will display a hyperlink that does the same thing but matches it to the Bank Import sheet. Logical Test (checks if it contains "Bank"): Value if True (creates hyperlink to the mismatch amount on the Tracker Import sheet): Value if False (creates hyperlink to the mismatch amount on the Bank Import sheet): This works perfectly fine but when I do it as a spill formula, the links either don't work or take me to the incorrect sheet/cell. Formula (Flash Filled range $K$3:$K$1000 - working): =IFERROR(IF(ISNUMBER(SEARCH("*Bank*",I3)),HYPERLINK("#'Tracker Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3,FIND(" ",I3)-1)),'Tracker Import'!$D$2:$D$1000,0)+1,4),"View Log"),HYPERLINK("#'Bank Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3,FIND(" ",I3)-1)),'Bank Import'!$F$2:$F$1000,0)+1,6),"View Transaction")),"") Formula (spill from range $K$3 - not working): =IFERROR(IF(ISNUMBER(SEARCH("*Bank*",I3:I1000)),HYPERLINK("#'Tracker Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3:I1000,FIND(" ",I3:I1000)-1)),'Tracker Import'!$D$2:$D$1000,0)+1,4),"View Log"),HYPERLINK("#'Bank Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3:I1000,FIND(" ",I3:I1000)-1)),'Bank Import'!$F$2:$F$1000,0)+1,6),"View Transaction")),"") Notes: • Range 'Tracker Import'!$D$2:$D$1000 is an Amount column that lists logs from my Spending Tracker. This range is just values. • Range 'Bank Import'!$F$2:$F$1000 is an Amount column that lists transactions from my bank statement. This range is just values. • If the Mismatch List says an amount is not found in the Bank, that means it's an existing amount in the Tracker that shouldn't be there, and vis versa. I would highly appreciate any solution to make my current formula a spill formula. Thank you!
Making data add to a new row in the same sheet
I have an excel sheet that we input data at the top weekly and it also pulls from other tabs. We update the numbers weekly, along with other data, into rows below and keep the previous data in rows above the current week. so for example: B27 would need to be F2 when we update it but B26 would still need to stay what F2 is now. Same thing with C27,E27,F27,G27,I27,K27. All of those pull from data at the top and change weekly. What formula could i use to make this happen? Help!
Performance issues with multiple slicers in "Analyze in Excel" (Power BI Live Connection)
Hi Excel Community, We are experiencing performance challenges when using "Analyze in Excel" connected live to a Power BI Semantic Model, particularly when we have a large number of slicers applied to a Pivot Table. Through our analysis, we've observed that when the "Visually indicate items with no data" option is enabled for these slicers, each slicer interaction seems to trigger at least two additional MDX queries. What's concerning is that these queries appear to run sequentially, meaning that with more slicers, the delay for each filter change becomes significantly longer. We wanted to ask the community if this sequential query execution for each slicer with "Visually indicate items with no data" is the expected behavior in the current implementation of "Analyze in Excel." Has anyone else encountered similar performance issues with multiple slicers in this scenario? If so, have you found any workarounds or best practices to mitigate these delays? We're particularly interested in understanding if there are alternative approaches to achieve the "visually indicate items with no data" functionality without incurring this sequential query overhead. Any insights or experiences you can share would be greatly appreciated. Thanks!
