Forum Widgets
Latest Discussions
Create a Power query or Dax to find Open Status more than 1 hour
Hi Team, I have the door table Here I want to find that whether door is being open continuously(having open status & not closed) for more than 1 hour for each DeviceId. Data Description: The Status column shows whether door is open or Closed. CreatedOn column shows time duration. DeviceId column shows Deviceid. My first requirement is: We need to add the minutes(createdon) between each row for same deviceid where the status = open. But where a status = closed (if closed comes in between open) we reset the number of minutes. So we sum by the previous value where status = Open - Something like If current status = open and previous status = open then sum the time and do the below: Sum(previous value + new sum) Where/if status = closed set time count = 0 Sum(previous value + new sum) is going to take into account only the consecutive open status >60 min for same divice id whenever status is closed, this Sum(previous value + new sum) will be reset to/equal to 0 So on the first open status, the count will be 0. Then the next open status it will be time between 2 open statuses, then on the third it will be the time between two open statuses add the previous time.but any close status then time is reset. Could you please help me with a power query logic/dax logic? Second Requirement: This is only a sample data. But we have billions of rows of data in our dataflow that comes from SQL views. So, thought not to use DAX for this calculation in future. we can precalculate the maximum open state duration by preprocessing data (SQL, Power Query(dataflow), Spark, anything else...). So can you help me prepare data upfront. we need to solve the problem with a with a proper data model. for example, create a table with the longest open events upfront (in whatever technique you are comfortable with) Could you please help me to achieve this? PFA file here B&M.pbix Thanks in advance!Excellove15Feb 14, 2025Iron Contributor24Views0likes1CommentDax to find open status for more than 1 hour
Hi Team, I have the below table: Here I want to find that whether door is being open for more than 1 hour or not. The Status column shows whether door is open or not. CreatedOn column shows time duration. Could you please help me create a dax logic to achieve this? PFA file here B&M.pbix Thanks in advance! SergeiBaklanSolved64Views0likes5CommentsHelp creating a matrix table
Good day wonderful people, I am trying to create a matrix table that shows correlations between types of issues people may be worrying about. The end result is to enable me to bring it into Power BI and utilize the chord visualization diagram to show where certain issues are connected and the strength of those connections. I have an excel sheet with a range (se below). The first column is a unique client ID. As the data may be collected over a period of time, the same client IDs may appear more than once (if they come back to the service provider). The data may include up to a maximum four issues that they are worrying about, and these are in adjacent columns to the client ID. As an example, in the below table, homelessness has low level interconnectivity with 'Priority Debt'; 'Disabled' & 'Mental Health' as they appear as issues alongside with that string; "Mental health" has a strong connection to "Non-Priority Debt" (and of course vice versa) with it occurring each time the other is mentioned. This is what I am aiming to show. Due to the way in which this data is laid out (from a database export), I am struggling to bring it into a matrix table (if that is the best method) and then on into Power BI. I've spent quite a bit of time in the forums, but can't find a way to apply the learning in them to the table output, or the right way to transform the data (via power query or otherwise) to help identify the interconnectivity. If anyone can point me in the right direction to solve this, I would be most grateful! Thank you in advance :-)138Views0likes8CommentsIncorrect Name Syntax
Good Morning, I am new to Excel (watched one video in Training Session). Doing homework by using Excel Inventory Template. I have been copying and pasting to add to the number of cells in the Worksheet. I have 3 open worksheets. (They are all frozen) I attempted to highlight an entire column to remove the cell's contents and got an error above that cell column '@[Quantity in stock]]. After that, every time i tried to write in a cell or highlight, I got the dancing broken lines and in the formula bar the message '=[@[Unit p+H87:H111rice]]*[@[Quantity in stock]]' and the error message ' The syntax of this name isn't correct. Verify that the name: -Starts with a letter or underscore (_) -Doesn't include a space or character that isn't allowed -Doesn't conflict with an existing name in the workbook. I had nothing to do with the creation of the formulas. Microsoft Support asked me to cross reference the formulas with the original document but I don't have any idea how to. She then forwarded me here. I have over a hundred rows of information inputted in the columns and sincerely hope my situation can be remedied to save the day. Thank YouDiyahFeb 14, 2025Occasional Reader13Views0likes1CommentProblem with LOOKUP function.
I have a workbook which keeps track of money in an account. I use the LOOKUP function to lookup the most recent date in one column and return the corresponding value in another column thereby telling me the current sum in the account. So far, so simple. However, I have discovered that if the sum in the second column is larger than the date in decimal in the first column, it will return the second largest sum. See the attachment for the relevant data. For reference, 45702 is the decimal for 14th February 2025. If I use the function =LOOKUP(MAX(A2:A50), C2:C50) I would expect £47,447.29 to be returned. However, it returns £45,658.85. I adjusted the figures to find out what the largest sum was that the function would return and in this case it happens to be £45,702 which happens to be the decimal format for 14th February 2025. If I increase the date, the maximum returnable sum increases accordingly but can never be greater than the date in decimal. Is there a valid reason for this? Am I making a schoolboy error of which I'm unaware or is there another reason? Thanks in advance.rt2963Feb 14, 2025Copper Contributor19Views0likes3CommentsKeep conditional formatting range when inserting/deleting cells/rows/columns?
Hi, I sometimes use conditional formatting. For each entry, there's a cell range that it applies to. Often I need it to be used on the entire sheet, or at least a large range of it, i.e. all rows that have content. But then I need to insert or remove data, and that mucks up the range. It seems to copy the entry to the range above, the inserted range, and the range below. I want it to keep the range and entries as they are when I edit the sheet's data. How? I usually enter the range as e.g. $1:$1048576 or $A:$XFD.SolvedKjell RilbeFeb 14, 2025Copper Contributor144KViews3likes41CommentsReturning a cell reference.
I've searched through all the Excel functions and can't seem to find a way to return a cell reference from a formula if that makes sense. For example. I have 5 in cell A1, 10 in cell A2, 4 in cell A3 and 8 in cell A4. In another cell I have the formula MAX(a1:a4) and that will return 10 which is the largest number in the array. I would like to know if there is a function I can use to return the reference of the cell instead of the value so that the formula would return $A$2 or whichever cell contained the largest value in the array.rt2963Feb 14, 2025Copper Contributor52Views0likes2CommentsNew excel file cannot open because the file format or file extension is not valid
Hi. When I right-click on my desktop and create a new Excel file and try to open it, it gives the error "Excel cannot open the file 'New Microsoft Excel Worksheet.xlsx' because the file format or file extension is not valid" I have seen other threads and websites on this, like these two: https://www.stellarinfo.com/article/excel-cannot-open-the-file-because-the-extension-is-not-valid.php#article2 https://techcommunity.microsoft.com/t5/excel/excel-cannot-be-open-the-file-filename-xlsx-because-the-file/m-p/1504346 however, none of the methods helped me. I am not trying to recover any data just want to fix the issue. I can open other existing excel files and can even create a new workbook from the excel app when its opened but I want to know why this is happening and how to resolve it. Your help is appreciated. Thank you.fayaazFeb 14, 2025Copper Contributor5.1KViews2likes6CommentsImport Tab Delimited File from Clipboard
I'm looking to create a macro in excel to import a file from clipboard. I have tried to record a macro and that did not work. I do not want to enter a file name. I would like to copy from notepad and execute macro to import the data with pipe delimits. Sorry if this is a repeat, I ould not fine an answr anywhere. Thanks.JimKFeb 14, 2025Occasional Reader17Views0likes1Comment
Resources
Tags
- excel42,207 Topics
- Formulas and Functions24,466 Topics
- Macros and VBA6,353 Topics
- office 3655,939 Topics
- Excel on Mac2,620 Topics
- BI & Data Analysis2,335 Topics
- Excel for web1,879 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,609 Topics