Complex formulae with data model
Over the years, I have developed a significant excel data sheet which has gotten slower and slower with time. I have discovered the idea of excel's data model this week and have learnt how to import the data I need from its source CSV to the data model and I now have a power pivot which looks like what I originally had in excel. What I would like to do next, is to replicate some of the formulae I had in excel to calculate if something should be counted or not. I would not want the red one to be counted as this row would have been counted in the previous month. In excel I was using the following formula- =IF(COUNTIF(B5:M4,">0"),SUM(Patient!B5:M5*0),Patient!N5) is there a way of achieving this with the data model or would I have to hardcode this into another sheet? I was trying to avoid doing this to minimise the number of formulae... Many thanks for your help...
unpivot data and handle merged cells without using Power Query (Unpivot_Toolkit)
Hey, guys!! I’ve been working on a set of functional Excel Lambdas to solve a common headache: transforming "Wide" human-readable data into "Long" database formats without having to open Power Query every time. =UNPIVOT_PLUS(table, [no_of_cols], [no_of_headers], [attribute_names], [value_name], [remove_errors], [remove_blanks],[pad_blanks_with],[pad_errors_with]) Don’t worry about the full list, most use cases only require 5 arguments. I've included a table of default values at the end. Merged Cell Support: Automatically handles fill-down/fill-right logic for merged headers/columns. Bonus Helper: SPLIT_INJECT =SPLIT_INJECT(array, target_indices, delimiter, [ignore_empty], [match_mode], [pad_with]) It targets specific columns, splits them by a delimiter (like TEXTSPLIT), and expands the entire table horizontally while keeping all other columns perfectly stable. Optional arguments match TEXTSPLIT defaults. Feel free to tear this apart or adapt it for your own edge cases. I’d love to hear how you end up using it! You can grab both functions from my GitHub Gist https://gist.github.com/Medohh2120/f8553c149684e39bb499249e39f01017 File with use cases https://1drv.ms/x/c/6c310c8fd1669a94/IQCscpo3yh7jR5XdGZe2AQGyAf4-vCd8K6BvLZQgrP2V8Oo?e=gPdbOd Argument Description Default Behavior table The array or range of data to unpivot. Required argument (no default) [no_of_cols] Fixed left columns to keep as identifiers. 1 [no_of_headers] Top rows used as headers, handling merged cells. 1 [attribute_names] Header name for the unpivoted attributes . "Attribute" [value_name] Header name for the unpivoted values. "Value" [remove_errors] Excludes grid rows with formula errors. FALSE [remove_blanks] Removes grid empty cells and empty strings. TRUE [pad_blanks_with] Value to substitute for empty cells. Leaves cell blank [pad_errors_with] Value to substitute for errors. Leaves error as-is
Excel 365 (version 2307&higher) crashes when refreshing pivot tables (more pivot tables at once)
Dear all, I am currently experiencing the following problem in Excel 365 version 2308 with pivot tables (on MSAS connections): Excel always crashes if I'm refreshing data for all connections for more pivot tables at once - especially when I have 2 pivot tables with the same connection on 1 sheet . This happens always :o(. Excel will NOT crash under any of the following situation, 1. I have to change some filter on each! pivot table (without refreshing); 2. After that I can refresh pivot tables. I must do the same after opening Excel file next time. I have tested it on multiple machines and the symptoms are consistent over all the machines with Excel 365 version 2307&higher. Stable workarround is NEXT: 1. I must have PC with lower version of Excel (I have old notebook with Excel 365 version 1808 at home). 2. Change connections to local PC with local MSAS (I have no connection to original MSAS from local PC). 3. Open file & refresh all connections. 4. Save & copy to PC with Excel 365 version 2308 5. Open file & change connections to original MSAS 6. After that refresh works fine. Now this file will work in Excel 365 version 2308. Can you kindly advise if anything can be done to this issue? - I have many Excel files on version 2308. Thank you in advance.
Correlation Study with Filters
Dear Experts , I have a data like below( Attached worksheet) And want study the correlation between SNR[0~3], there will be 6 combinations as below:- I want make like this but with Filters/Slices for the "File.Name" & "SSB or TRS" & "Carrier Index", different colors for different Carrier Index(0 &1) Thanks & Regards Anupam ShrivastavaFilter Function or TAKE-DROP Function
Dear Experts, I have a Data like this:- Column A -> Has the File Names, and Column B,C,D have their corresponding data, In Column F I have the unique File Names and from G/H/I -> I need the start of the hfn/sfn/slot and in J/K/L the end of the hfn/sfn/slot for that File Name as populated , How to achieve this? Thanks & Regards Anupam ShrivastavaCleaning messy excel/csv form ERP system
Hi, I’m curious how people here usually deal with messy exports coming from ERP or accounting systems. I often receive Excel or CSV files that are not really ready to use as a table. Things like: -a few rows of system info before the header -totals or notes mixed inside the data -different date formats in the same column -currency symbols and formats all over the place -sometimes even another small table sitting under the first one Before doing any analysis (excel, power BI...) I usually have to spend a bit of time cleaning the file so it becomes a normal structured table. Power query definitely helps, but depending on the export I sometimes still end up doing several cleanup steps first. I’m curious what the usual workflow is for people here. Do you normally build a reusable Power Query transformation for these reports, or do you handle each export differently? I recently walked through one messy export example here while documenting the cleanup steps if anyone is curious about the kind of file I mean: https://www.mubsiraanalytics.com/blog/how-to-extract-clean-tables-from-messy-excel-csv Mostly just interested to see how others approach this.Conditional Formatting or a Specific Filter Rule
Dear Experts , I have a data like below:- here the Column-O, txNumber can go from 1,2,3 (txNum=2 and 3 means a Retransmission), I want to filter all the pairs for all Transmissions and their corresponding Retransmissions ( either by coloring them all using a conditional formatting) or using a specific filter Rule. How to identify the Transmission and it's corresponding Retransmission for a Specific HarqId( say for dlHarqProcessIndex ==7, the blue color Row-37 is my 1st transmission(txNumber ==1) for the dlHarqProcessIndex==7 with a tbSize of 852696, and adaptRetxStatus == NOTACTIVATE, and it's corresponding 2nd transmission( Retransmission , txNumber==2) Row-45, tbSize remains same(as it's corresponding 1st transmission) in all retransmissions and adaptRetxStatus changes to "APPLY" for all retrans txNumber==2,3) and similarly for other pairs like for dlHarqProcessIndex==13. Attached is the Worksheet.
Excel Map - One region is recognized but not showing up in color
Hi, As shown below, I am trying to map out the number of farmers in our data in each of Georgia's regions. Samegrelo Zemo Svaneti is recognized and the data label appears but the color does not. If I change the map to show only the regions with data, it does show in color but I need the full country map.
