How to transform wide-format data into the structure shown in 'Result' sheet using Power Query?
Hi everyone, I have a dataset in wide format in the "Data" sheet, and I’d like to reshape it using Power Query to match the format shown in the "Result" sheet. What the source looks like (Data sheet): Row 1 contains repeating column headers for measures like Sales, Purchase, Sell value, etc., grouped by date: | Product Brand | Product Name | Region | 01.05 Sales | 01.05 Purchase | ... | 01.06 Sales | 01.06 Purchase | ... | Note: The values in the file are randomly generated using a function, just for demonstration purposes. Rows where the Region column contains values like a1_1, a1_2, a1_3 are subregions, and the row with Region = a1 is the sum of those subregions for that product. What I want to achieve (Result sheet): I need each value in a separate row, with the following columns: Product Brand Product Name Region Date Measure Values Example: Product Brand Product Name Region Date Measure Values Brand A Product A_1 a1 2025-05-01 Sales 15 Brand A Product A_1 a1 2025-05-01 Purchase 22 Brand A Product A_1 a1 2025-05-01 Sell value 32 The Excel file contains two sheets: "Data" – raw data "Result" – expected outcome Can anyone help me write the proper Power Query steps (M code) to achieve this transformation? Thank you in advance! https://docs.google.com/spreadsheets/d/1nMz_TyXRSQNelq-cbUDfSf8ekKezGCTn/edit?usp=sharing&ouid=109036053433195029380&rtpof=true&sd=true
Formula 4 week rotation excluding Fridays and weekends
Hi. I'm creating an employee work calendar. I need to formulas. One i have it but the other one i cannot figure it out. Employee A works from home, called W, one day a week. Week 1 on a monday, week 2 on a tuesday, week 3 on a wednesday, week 4 on a thursday, week 5 on a monday and so on. Fridays not available and saturdays and sunday are weekends. My formula only works if the first of the month is a monday. So i cannot do another month plus i cannot figure it out for employee B when the working from home is a Tuesday for week 1, can someone tell me what i'm doing wrong?
Better model to improve the performance calculated table/Dax measures
Hi Team, Now, I have an issue with Data model I have developed. Attached is the model i use for my power bi reports that contains 3 dashboards. Invoice quality is one of the dashboards. Below are the requirements to be created for visuals: For the Data[DBName-Point_Id] & Data[Date] columns from Data table, we need to find the status(Dax) as below: *Credit Cancels Invoice -- Number of Credits = Number of Invoices && net cost = 0 *Invoice-Credit Value Mismatch -- ISEVEN(Net invoices) && Net cost <>0 *Multiple Credits -- Number of Credits > Number of Invoices && Net cost <>0 *Multiple Invoices -- Number of invoices >1 && Number of invoices >Number of Credits We need to use this Status dax in visual y-axis of stacked bar chart and use the Count of Dates from Data table as x-axis: Number of Credits,Number of invoices, Net invoices and Net cost can dax or a calculated table columns. Currently, we have created a calculated table using dax logic as below: Invoice Issues = CALCULATETABLE ( SUMMARIZE ( Data, Data[DBName-Point_Id], Data[Date], "Invoice Issue", SUM ( Data[Invoice / Credit] ), "Number of Invoices", CALCULATE ( COUNT ( Data[Invoice / Credit] ), Data[Invoice / Credit] > 0 ), "Number of Credits", CALCULATE ( COUNT ( Data[Invoice / Credit] ), Data[Invoice / Credit] < 0 ), "Net Cost", SUM ( Data[Cost] ) ), Data[Source] = "Invoice" ) Based on the above table, Status calculated column in created in same table: Status = IF ( 'Invoice Issues'[Number of Invoices] > 1 && 'Invoice Issues'[Number of Invoices] > 'Invoice Issues'[Number of Credits], "Multiple Invoices", IF ( 'Invoice Issues'[Number of Credits] > 'Invoice Issues'[Number of Invoices] && 'Invoice Issues'[Net Cost] <> 0, "Multiple Credits", IF ( ISEVEN ( 'Invoice Issues'[Net Invoices] ) && 'Invoice Issues'[Net Cost] <> 0, "Invoice-Credit Value Mismatch", IF ( 'Invoice Issues'[Number of Credits] = 'Invoice Issues'[Number of Invoices] && 'Invoice Issues'[Net Cost] = 0, "Credit Cancels Invoice", BLANK () ) ) ) ) This calculated table is then connected to Points and calendar table DBName-Point_id(points table) --DBName-Point_id(Invoice Issues table) Date (Calendar table) -- Date (Invoice Issues table) This setup causes performance issues especially Status column. Is there any better ways to model this to improve the performance? please let me know if you need further info! PFA file here PR-419 - Data Coverage - Copy (4)_Daxoptimize_copy.pbix Thanks in advance! SergeiBaklan
Button for reset all filters
Hi, First of all I am not at good at this but I looking for a way to use a button to reset all the filters I have. To use one button instead of clear all the filters separately would be great as it is more effcient. But as I wrote, I am not very good at this I was wondering if someone could show/teach me how to do it.Move up to next blank row after copy/paste from previous sheet.
Snowman got me rolling with code that does almost exactly what Im trying to do. After I hit end of day button Im trying to get the copied data to move up to the next blank cell in column B within a range. If I have any data in B2:B9,B11:B21,B23:B29 marked with a t next to it in column A and hit the end of day button only data in cells marked with that t are moved to the next sheet in the same cells they came from. What Ive noticed is only data in B2:B9 go to the exact same cell. B11:B21 are offset by 1, and B23:B29 are offset by 2. This is fine as the data is still moving. What I want it to do is for those ranges move up to the next blank cell in column B. So if I have data only in B4, and B6 I want that to transfer to next sheet in B2,B3. The same for the other 2 ranges. I also dont want it to overwrite anything that may already be in a cell on the next sheet. If I have "Tree" in B2 of the next sheet I want the data to go to B3,B4. Same for the other two ranges. I have tried xlUp, xlDn, and played with the code that was given to me by Snowman to try and make it work. Im not getting anywhere with this. I thought maybe if I create another macro that after I transfer the data would move everything up into blank cells then Id be okay with that also. Im not having any luck with that either. I even recorded a macro for copy/paste but that wont work if there is data in a cell already that I need to keep, and not be overwritten. I have attached a copy of the workbook. Hopefully this time it will allow it to be published with this query.
Help needed Exel Newby
I want to make a date range in Exel. if something is due to run out in less than 15 days I want it to be red if something runs out in 90 days I want it to be green and if something is runs out in 15 days to 90 days I want it orange. how can I do this? where can I find simple functions?
Can't find a way to absolute reference a column name.
So I'm making a table that will give me the value from another table on a different sheet, this sheet constantly gets updated and the column order are always different so I need to use a table name. I was able to make column C work by making A2 absolute Is there a way to make D2 and everything on the right show with the lookup array absolute if I drag them to the right? D2 should give me "SET {SET}" with below: =XLOOKUP($A2,Live[Code],Live[Unit_SNG]) Thank you!Excel Formula, combining two IF statement
Hello, I need to combine 2 if statements: =IF (AND (C2 >499999, E2=3010), 3080, 3050, =IF (AND (C2 >499999, E2=3020), 3090, 3060 I want to create a Formula to check if value in cell C2 is greater than 499999. If it is, then checks if the value in cell E2 is 3010; if true, it returns 3080, otherwise it returns 3090. Formula also checks if value in cell C2 is greater than 499999, and value value in cell E2 is 3010; if true, it returns 3080, otherwise it returns 3090. Value in E2 will always be either 3010 or 3020.
