Displaying non-additive measures in Power BI visuals
Displaying non-additive measures in Power BI visuals
Customized dynamic time matrix visual in Power BI
customized dynamic time matrix in Power BI using DAX
Share this:
Hiding and Locking only cells with formulas in Excel
Hiding and locking only those cells/range that have formulas in Microsoft Excel.
Share this:
A smarter multi criteria VLOOKUP in Excel
Performing multi-criteria lookup in Excel. The combination of VLOOKUP and CHOOSE functions does the magic elegantly.
Share this:
Displaying hyperlinked data in table or matrix visual in Microsoft Power BI
Step by step instructions to hyperlink field values in table or matrix visual Microsoft Power BI Desktop.
Share this:
Office Scripts: Is it the beginnings of the end of VBA era?
It has been quite some time now a lot of talk around the future of VBA. Here comes the Office Scripts in Microsoft Office web apps like Excel (in its preview) with two important features to seed the beginnings of the end of VBA era:Â Actions recorder: Mimicking VBA macro recorder functionality Automation scripting: Mimicking... Continue Reading →
Share this:
Infographic of Table Merge or Join Operations
Most data analyses start with organizing the data, at least starting with organizing in tabular format. This implies tables are the most fundamental formats to deal when analysing data. Here I crafted an infographic showing the most commonly performed merge or join operations among tables. Hope you will find this useful. Feel free to share... Continue Reading →
Share this:
Cascading or dependent drop-down lists using Data Validation tool in Excel
If you are thinking of bringing interactivity to the reports in Excel, you may have come across the need for having a simple drop-down list or a more complex cascading drop-down list (i.e., a drop-down list whose values are dependent on the value already selected in another drop-down). Example Scenario: For example, say, you have... Continue Reading →
Share this:
Calculating Age (duration or difference between two dates) in years, months and days in Power Query M Script
16-Jul turns out to be the birthday of Microsoft Power BI. Here I post a formula in M Script to calculate the age (i.e., duration or difference between two dates) in years, months and days: let Source= [ PowerBIBDay = #date(2014, 7, 16), Today = Date.From(DateTime.LocalNow()), BirthdayFactor = if Date.DayOfYear(PowerBIBDay)<Date.DayOfYear(Today) then 0 else 1, DayFactor... Continue Reading →
Share this:
A magical Excel trick for flattening data copied from a PivotTable report in tabular layout into usable format
Here is a scenario that I come across sometime ago when I received an Excel file with some apparently messy data for further processing to generate another report. The data in the file appear similar to below screenshot. This data is self-explanatory. It’s the sales in units and dollars of each product under each product... Continue Reading →
