Suppose there are two ranges A2:A4 = A, B, C B2:B5 = 1, 2, 3, 4 Cartesian product should give the result A1, A2, A3, A4, B1, B2, B3, B4, C1, C2, C3, C4 In Excel the formula would be =DROP(REDUCE("",A2:A4, LAMBDA(a, b, VSTACK(a, b & B2:B5))),1) In Power Query, the formula would be =…
Category: Power BI
Power Query – Bypass 3000 cells limit in Enter Data – Tips & Tricks 19
Question – I am trying to "Enter Data" in Power BI that is a large table. I am doing this to avoid a connection and avoid refreshing data. I can copy the data from a csv file but when I paste into Enter Data, I get an error with 3000 cell limit. Answer – 1. Import…
Power Query – Multi Column Exact Match XLOOKUP/VLOOKUP – Tips & Tricks 18
Learn to perform Multi column exact match in Power Query like XLOOKUP, VLOOKUP in Excel. The pbix file used in this can be downloaded from – https://1drv.ms/u/s!Akd5y6ruJhvhvxUlfh5W15hhMqT-?e=vg6eTL
Power Query – Exact Match XLOOKUP/VLOOKUP – Tips & Tricks 17
I wrote a tip earlier on this – VLOOKUP/XLOOKUP Formula – Power Query – Tips & Tricks 7 Here it the video. Learn to perform exact match in Power Query like XLOOKUP, VLOOKUP in Excel. The pbix file used in this can be downloaded from – https://1drv.ms/u/s!Akd5y6ruJhvhvwxUuHqOA1XAZ33u?e=8ZDAik
Power Query – Date.IsInQuarterToDate, Date.IsInMonthToDate and Date.IsInWeekToDate functions – Tips & Tricks 16
Power Query's M language provides a rich collection of Date functions. One of the functions offered by Power Query's M language is Date.IsInYearToDate function. This function will give True or False depending on whether date is within Year to Date range. Hence, if today's date is 28-Nov-22, then dates from 1-Jan-22 to 28-Nov-22 will be…
Power Query – Make Text.ExcelTrim Function – Tips & Tricks 15
TRIM function of Excel and Text.Trim functions of Power Query both remove leading and trailing spaces (ASCII Char code 32) from a text. But Excel's TRIM goes a step further. If there are mulitiple spaces between words, it will reduce those to a single space between words. Hence if I have a string " Microsoft…
Power BI – New DAX functions launched in 2022
This is a mini guide to recap those 9 functions which were launched in year 2022. PDF file corresponding to this video – https://1drv.ms/b/s!Akd5y6ruJhvhvwCdltcqWqFlen0f?e=cAzGyg
Power Query – Generate Fiscal Year – Tips & Tricks 14
Suppose you need to generate fiscal year like 2021/22, then use this formula in a custom column where 3 means FY finishes in March month. Similarly say your financial year finishes in Jun month, replace 3 with 6..If it finishes in Jul, replace 3 with 7. You will need to replace [Year] and [Month] fields…
Power Query – Sum only Numbers in a List – Tips & Tricks 13
Suppose you are given a list {"x", "100","200", "x300", 500} and you need to sum only the numbers 100, 200 and 500 in it. Then you can use following code
Important – Content of this Website is 100% Copyright Free
I just want to convey that the content of this site is completely copyright free. You can use the content the way you want without seeking any permission. Whether you are using it commercially or non-commercially, personally or within your organization, it doesn't matter. There is no need to give any acknowledgement either that you…