Creating Sankey Diagrams
Hi, Does anyone have any guides for creating Sankey Diagrams in Excel. I deal with the analysis of a reasonably long process which has various customer inputs and outcomes. I am looking for a visual way to show everyone who came into the process, and what happened to each person. Sankey Diagrams seem to fit that bill perfectly. Any help would be appreciated. Thanks T
Creating Logic to find whether columns in one table matches other columns in other table
Hi Team, I have 2 tables: Door table as below: Store Status table as below: They are modelled as below (let me know if we can better model it-suggestions are welcomed): Now, I need to create a logic(Breach) to find when Door Table - Status column being Open when Store Status table - Status column being Closed (indicated by - 'C'). During this logic, we have to make sure it satisfies below conditions: DateTime column of Store Status table should match the createdon column of Door table Store id column of Store Status table should match the siteid of Door table Output should return 1 if the conditions meets else 0. Could you please help me create a logic for this? PFA file here B&M.pbix Thanks in advance! SergeiBaklanCreate 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!Dax 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! SergeiBaklan
Excel Maps not showing all options for regions
Good morning , I am trying to generate maps with excel, however I don't visualize the possibility to select Multiple countries/regions and Countries/regions, only World and regions with data. The first screenshot is how it should be, the second is my Excel program. Does someone know why? thank you for any help (consider that I am at Excel for dummies level... ) Marinaif the value in one list exists in another array.
Hello, How can it be made to change all the data at once without breaking the formula? Excel file is attached. İF(A8:A109 (bir değer) = Girişler!Y2:Y26 (if on this list); (does not take any action. Therefore, all multipliers become 1.) ; İNDİS(OQ4:PB4;SIRALI(1;9;1) ) (If it's not in the list, you run this formula.) ) =EĞER($A8:$A109="";"";LET( itemCode;PTC!A7:A109; start;KAÇINCI(Mix!$D$1;PTC!$C$7:$K$7;0); BlockStart; KAÇINCI(OQ5;PTC!5:5;0)+start-1; dataPlus;FİLTRE(PTC!7:109;((SÜTUN(PTC!5:5)>=blockStart)*(SÜTUN(PTC!5:5)<=blockStart+8)) range;BIRAK(dataPlus;1) ) / İNDİS(OQ4:PB4;SIRALI(1;9;1)) ; veri;BIRAK(aralık;;-1); dataSonraki;BIRAK(aralık;;1); çeyrekler;BIRAK(AL(dataPlus;1);;-1); sonuç;(data-dataNext*(SAĞDAN(çeyrek)<>"3")); sonuç/$A$2 )) I want results like yellow products where I want them.
Tracking Changes
Hello, I need to track changes on a shared excel document and I have followed the steps on how to create the shared workbook to track these changes however it simply does not highlight any changes made by anyone other than me. I shared it with a colleague and watched them edit and save the document they then closed it, but when I opened it and selected Track Changes > Highlight changes, it just notifies me that no changed were found. I am certain there must be an easy answer to this however I cannot find it, hopefully someone can tell me where I am going wrong. Thanks
Automatically Copying text from sheet to sheet
How do I get text on one sheet to automatically fill into another sheet on the same workbook? I'm trying to make a workbook that will allow me to type a scope of work for a job into an estimate sheet and have it automatically fill into a scope of work sheet that is specifically for printing out.
