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 Contributor5Views0likes0CommentsDax 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! SergeiBaklanSolved48Views0likes3CommentsImport 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 Reader12Views0likes1CommentImpossible to sum numbers in Excel
I hope someone can help with this because I am pulling my hair out. When I try to sum numbers in a column, at the bottom of the page it gives me a count and not a sum. When I try to Autosum, it gives me =SUM(). When I try to add the specific cells, e.g. =SUM(E2:E15) it gives me zero. I've read through multiple forums which suggest to make sure the cells are formatted as numbers- which they are. Could someone help me out here because I am completely at a loss. Thanks.philip369Feb 14, 2025Copper Contributor152KViews0likes53CommentsAnalysis ToolPak Chi Squared test for independence
I recently had someone show me that through Analysis ToolPak, a chi-squared test can be performed in excel similar to other statistical programs such as SPSS. She just created the 2 x 2 contingency table and used data analysis to perform the function; however, after adding it into my personal computer (i have microsoft 365 Family), i cannot find the function under the data analysis section. Is there an easier way to do a chi squared test for independence that does not include working through all of the steps in excel or a good statistical add in which will perform the functions for me so I can work through the data analysis quickly.elirodriguez2015Feb 14, 2025Occasional Reader9Views0likes0CommentsWhat causes an excel file to 'Show Print Preview' rather than show the preview by default.
I am curious what causes this to happen, I have already set the print area etc., but for some reason I don't get an immediate preview when going to print, I have to click this button inside the print preview. (I have also attached the full screenshot Doesn't seem to matter what settings I change so far, just curious what exactly causes this change in excel, I have many other documents that just bring up the preview straight away when going to print, but at the moment, this one particular file I have to click another button. I have a MS flow that converts excel files to pdf automatically after a review process, but it keeps failing on this one file and I can't figure out why, but I think the key revolves around this, so I really want to find out what causes excel to present an additional button that you have to click to generate a preview rather than showing the preview off the bat like it does with every other file which works fine in the flow. ThankyouSolvedModulaFeb 14, 2025Brass Contributor64Views0likes5CommentsExcel help
Hi all, I have list of LGA location names in excel file. An example are as follows: Currently after I extract the data, I use look up function to get the New LGA column to replace the existing data. This is a manual task. The original file I have download from MS SQL and the file format is csv. Is there any other way that can do this more efficiently and automatically? Thankskpan8Feb 14, 2025Copper Contributor16Views0likes1CommentPermanently changing the default excel date format from dd-mmm to mm/dd/yy for all future files.
For as long as I can remember, I have had to manually reformat dates in excel from the default 20-Jun (dd-mmm) format to something that people actually use (never seen dd-mmm anywhere but as default in excel). Is there a way to never have to do this again by changing the default in all new .xls files I create? Not sure how the current default was chosen, but not something any person/company/business unit I have ever worked with/in has used. I would be forever thankful if I didn't have to manually reformat again :). Thank you!zookjonesFeb 14, 2025Microsoft3.4KViews1like4CommentsREMOVE A CHECKBOX FROM EXCEL WORKSHEET
I was handed an Excel worksheet that has three checkboxes that I wish to remove but cannot seem to remove them. Excel Help says to right click the Box and then hit Delete. There is no Delete in the ensuing dropdown menu and Delete on the keyboard does not work. Any suggestions?tjbCanton2020Feb 13, 2025Copper Contributor317KViews4likes22CommentsOptimizing Excel Formula for Dynamic VLookup
Hi, I have this formula: =IF(F2="", "", VLOOKUP(IF(VLOOKUP(F2, $A$2:$I$1400, 4, FALSE) = 0, VLOOKUP(F2, $A$2:$I$1400, 6, FALSE), F2), $A$2:$I$1400, 9, FALSE)) This formula is meant to fill in the Manager’s email. Rank 0: Every role below the Department Manager level is ranked as 0 in the 4th column (‘Rank’). Currently If an executive reports to a Senior Executive (Rank 0), the formula checks the 4th column. If the Senior Executive has a Rank 0, it looks at the 6th column instead (to get the employee number the Senior Executive reports to). Example: For employee ‘7586’ with email ‘email address removed for privacy reasons’, the formula currently fills ‘email address removed for privacy reasons’. It first looks at the 4th column of 1577, finds 0, then takes the value in the 6th column (0209) and uses that for the final VLOOKUP. Needed Change: I need the formula to perform an additional VLOOKUP. It should check the 4th column for employee 0209. If it’s still 0, it should look at the 6th column instead. Goal: The new formula should fill ‘email address removed for privacy reasons’ as the manager’s email for employee 7586, not ‘email address removed for privacy reasons’.8932LDGFeb 13, 2025Copper Contributor24Views0likes1Comment
Resources
Tags
- excel42,205 Topics
- Formulas and Functions24,466 Topics
- Macros and VBA6,353 Topics
- office 3655,939 Topics
- Excel on Mac2,619 Topics
- BI & Data Analysis2,335 Topics
- Excel for web1,879 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,609 Topics