Aggregated Intelligence

Running PdfPlumber in an AWS Lambda Function

2025-12-18T14:12:00.001-07:00

Here are the steps that I have documented for running PDFPlumber inside an AWS Lambda function. PDFPlumber is really good at extracting content (especially structured content) from a PDF. It can do this without the use of any GenAI models, but the downside is that it cant handle varying content very well. But if you have PDFs that have the same format and have well structured tables, then this tool can do a very good job at extracting that information.

mypublicnotes/AWS/Lambda/pdfplumber.md at master · rajrao/mypublicnotes

Infinite Campus stopped working on my phone (ERR_CONNECTION_TIMED_OUT)

2025-11-09T09:35:00.001-07:00

My kid goes to a school that uses Infinite Campus and I suddenly started getting an error where I was unable to log into the site.

I tried different browsers, clearing cookies and nothing seemed to help.

The error:

This site can’t be reached campus.dcsdk12.org took too long to respond.

Try: Checking the connection

ERR_CONNECTION_TIMED_OUT

In the Android app, I got a much less helpful error message about something going wrong with the connection. The odd thing was that the site and app was working for everyone else in my family.

Finally I was able to figure out that the issue was that I was on a VPN. Once I disabled the VPN, I was able to successfully use the site and app. Lucky for me, my VPN allows me to add apps to an exception list. Adding campus Infinity to the exception list in my VPN app fixed the connection issue for me.

SVGs, Floor plans in PowerBi

2025-10-31T13:05:00.002-06:00

We had a business requirement to show charts with regions. For the first use case we had to show body injury data and what we had was an SVG that had all the body injury locations.

We looked at a lot of solutions:

1. Storing the SVG data in a table/measure and displaying it.

This did not work, as the SVG data I could only get to display in a table visual and that would be very small for our use case. Additionally we wanted to be able to pick locations and display regions with color coding.

2. Using the ShapeMap.

I tried to convert the SVG vector to TopoJson, but nothing seemed to be able to convert the SVG to a good TopoJson. I even tried to trace the SVG into a vector file using QGIS and then convert it to a TopoJson file. But it always ended up looking like a "Picaso Painting"

See: powerbi - Power BI - Shape map rendering issues with TopoJSON - Stack Overflow

3. Custom Visual:

There are a few different custom visuals available and we were investigating Synoptic Panel from Okviz). This would probably work, but if you had a lot of shapes, regions, then it would cost a per user license.

Final Solution:

The final solution actually turned out to be the easiest (the only requirement is that you need to have Visio online).

Here are the details: Add Visio Visuals to Power BI reports - Microsoft Support

And here is what it can look like:

One reason that this is a really good solution is that if you have a drawing that might frequently change (example floor map), then all you have to do is edit the visio diagram in Visio Online and if you open up the PowerBi dashboard, the changes immediately show up.

How we did it: Its simple, create a new visio diagram and insert the SVG. As long as your SVG was made up of different parts, the svg will come in as a vector. You will have to ungroup your vector and you can make changes. For PowerBi to be able to color the image, the transparency for each part needs to be set to 0%. One thing I could not figure out is, how Power Bi determined as the page to use for the visual. It seemed like which one was the last one being edited would be picked. This felt dangerous, as if someone edited the diagram, it could impact the dashboard. So, we went with the option of a separate visio file for each diagram we wanted to include in PowerBi.

We will be investigating this option more, but I wanted to put this out there as a potential solution.

More info:

Custom Shape Maps in Power BI – Driven BI Data

Use Shape Maps in Power BI Desktop (Preview) - Power BI | Microsoft Learn

PowerBi + Aws Athena + Dataflow Error: Incorrect number of arguments

2025-05-30T16:00:00.005-06:00

A dataflow that I had suddenly stopped work.

The error was

Encountered user gateway exception: '<ccon>ODBC: ERROR [HY000] Incorrect number of arguments: </ccon>

After a lot of troubleshooting, I figured out it was being caused by some steps I had added to add additional columns. Most likely, those steps were being query-folded to the database and it was not working.

I solved it by adding a call to Table.StopFolding right before I added the steps.

Table.StopFolding - Table Function | Power Query M

AWS Lambda error: Sandbox.Timedout 3.00 Seconds

2025-04-09T11:25:00.002-06:00

In a AWS lambda, we suddenly started seeing this error:

{

"errorType": "Sandbox.Timedout",

"errorMessage": "RequestId: xxxxxx-xxxx-xxx-xxxxxxxxxxx Error: Task timed out after 3.00 seconds"

}

The "Sandbox.Timeout" threw me and I could not figure out where it was coming from.

Turned out, the error was being thrown by the AWS Lambda Infrastructure, because it was configured to run for only 3 seconds. This is done via the Configuration tab and editing the "General Configuration" and increasing the timeout setting.

PowerBI + PostGreSql + AWS-RDS

2025-04-01T13:11:00.001-06:00

I was getting an error when trying to connect using the PostGreSQL connector in PowerBi.

The remote certificate is invalid according to the validation procedure

The basic solution is informed by this AWS post: Set up SSL/TLS client connections to Amazon RDS for SQL Server and Amazon RDS for Oracle | AWS Database Blog, but I found it didnt work exactly the way in that post (PEM file didnt work).

Luckily for us, AWS now provides a PKCS7 file. So, go to Using SSL/TLS to encrypt a connection to a DB instance or cluster - Amazon Relational Database Service and download the bundle appropriate for your AWS Region. (You can use the global bundle, but the problem with that is you will have to hit approve in the Import Wizard many times (approximately 3 for every zone)). So, using your region's cert bundle will lessen the number of clicks.

Once downloaded, you will have to open your Windows Certificate Manager (Windows >> Run >> CertMgr.msc).

In the CertMgr, click on "Trusted Root Certification Authorities" >> Certificates and then Import.

In the Import Wizard, you will have to find the P7B file you downloaded from AWS and import it. Approve the next few steps. You should now be able to connect to PostgreSql running AWS-RDS.

These steps should also work for connecting SQL Server to PostgreSql in AWS.

Microsoft Forms: Difference between a Form and a Quiz

2024-12-11T15:37:00.003-07:00

In Microsoft Forms you can create a "Form" or a "Quiz". But what is the difference between the two?

In a quiz, you can specify the correct answer and the points for the answer, if correct.

Where for a Form, you cannot provide the correct answer, nor can you assign the question a score.

Also, with a quiz, you can do some cool things like enter equations for Math questions:

A quiz also has some additional settings that allow you to show answers, etc:

Select Blinds Ac 114 remote pairing/copy code to new remote

2024-08-17T13:41:00.002-06:00

Select Blinds Ac 114 remote pairing/copy code to new remote

Works for AC114-06B and 02B remotes.

Paired remote: the one that currently is programmed and working

New remote: the one you wish to program

Steps:

1. Select the channel on the paired remote that you wish to copy to new remote

2. Select the channel on the new remote

3. Press stop button once on paired remote and then press and hold until the blinds jog up and down once

4. Quickly on the new remote press up button. Blinds should jog to let you know the code got copied.

Manual: https://drive.google.com/file/d/1mcOqt3M_EZQjjxoddzVfIvbzJvrlqGQ3/view?usp=drivesdk

Windows 11 - Enabling Hibernate

2024-04-15T07:56:00.001-06:00

If Hibernate is not available as an option, the following 2 commands run from PowerShell should enable it:

1. powercfg /hibernate on

2. powercfg /h /type full

The above commands need to be run from a Administrator Powershell window.

In Windows 11, you can then go to: Start >> Type: Control Panel >> Control Panel >> Power Options >> Choose what the power buttons do.

AWS - MWAA - Customize UI Title

2024-01-18T18:00:00.002-07:00

If you would like to customize the Airflow UI title to include some additional information, you can do so in MWAA by setting webserver.instance_name

This will update the Title bar to show the value you set as shown below:

For more info see: Customizing the UI — Airflow Documentation (apache.org)

AWS Athena - Using Merge + Iceberg tables to store only changed records

2023-10-23T14:55:00.007-06:00

This post is based on my github page: mypublicnotes/AWS/Athena/iceberg_tracking_changes.md at master · rajrao/mypublicnotes (github.com)

Change data capture using Athena and iceberg

Many times in a datalake, you have a source, where the source doesnt provide information about which records changed. Another use case is where you have an ETL, where you have multiple tables and columns taking part in it and its traditionally difficult to track which records changed in that ETL query. This page shows you one method for being able to track those changes and insert only those records that are new or had updates. (at the end, I also show how to track deletes). The method leverages AWS Iceberg tables in Athena (Athena Engine 3) and the upsert mechanism provided via the merge-into statement.

TL;DR; Check out the merge statement used to update only those records that had changes.

Setup: A CTE for source data

I am using a CTE to simulate source data, in practice, you would typically use another Athena table as your source, or a query that brings data together from multiple tables (aka ETL), etc. A key part to this method is using a hashing function that can be used to determine when a record has changes. I use xxhas64

with cte(id, value1, value2) as
    (
    select 1,'a1','b' union all
    select 4,'raj','rao' union all
    select 2,'c2','d2' 
    )
    select *, xxhash64(from_base64(value1 || value2)) as hash from cte

Note 1: You can use murmur3 instead of xxhash64 using the following code: murmur3(to_utf8(value1 || value2)).

Note 2: Here are the other hashing functions available: https://trino.io/docs/current/functions/binary.html

Setup: Create an iceberg table

The iceberg table is your final table. This will track the data that had changes. Id is the primary key in this case, you can have more columns that are part of the primary key used for the update.

CREATE TABLE
  test_db.hash_test (
  id int,
  value1 string,
  value2 string,
  hash string,
  last_updated_on timestamp)
  LOCATION 's3://my_test_bucket/hash_test'
  TBLPROPERTIES ( 'table_type' ='ICEBERG')

The ##Merge## statement

Here is a merge statement that inserts new records and updates only when there are changes. The merge statement uses the CTE described above as its source data. You can manipulate the CTE to test various scenarios. The hash column is used to determine when to insert/update data.

MERGE INTO hash_test as tgt
USING (
    with cte(id, value1, value2, value3) as
    (
    select 1,'a1','b',100 union all
    select 4,'rao','raj',200 union all
    select 2,'c2','d2',300 
    )
    select *, xxhash64(to_utf8(concat_ws('::',coalesce(value1,'-'),coalesce(value2,'-'),coalesce(cast(value3 as varchar))))) as hash from cte
) as src
ON tgt.id = src.id
WHEN MATCHED and src.hash <> tgt.hash
    THEN UPDATE SET  
    value1 = src.value1,
    value2 = src.value2,
    hash = src.hash,
    last_updated_on = current_timestamp
WHEN NOT MATCHED 
THEN INSERT (id, value1, value2, hash, last_updated_on)
      VALUES (src.id, src.value1, src.value2, src.hash, current_timestamp)

If you need to deal with deletes, you can add as your first matched phrase one of the following options (delete, or archive):

WHEN MATCHED and src.IsDeleted = 1
  THEN DELETE

WHEN MATCHED and src.IsDeleted = 1
  THEN UPDATE SET  
    is_archived = 1,
    last_updated_on = current_timestamp

Finally some examples of queries to view the data

-- see the history of changes
select * from test_db."hash_test$history" order by made_current_at desc

-- use a snasphot_id from above as your value for xxxxx
select * from test_db.hash_test for version as of xxxxx

-- get only the latest records from the table
select * from test_db.hash_test
where last_updated_on in (select max(last_updated_on) from test_db.hash_test)
order by last_updated_on

Reference:

Testing Hashing Behavior

When hashing you need to make sure that null values are handled appropriately.

Ex: null, a, null and a, null, null should be treated as changes. If they generate the same hash, then you will miss this change. Also the hash functions need string input and hence, one needs to cast the data when its not of type string. For this reason, the computation of the hash gets complicated and I have not found a simpler solution around this.

with cte(id,note, value1, value2,value3) as
(
    select 1,null,'a1','b',1 union all
    select 4,null,'raj','rao',2 union all
    select 5,'both null',null,null,null union all
    select 6,'empty & null','',null,null union all
    select 7,'null & empty',null,'',1 union all
    select 8,'empty-empty','','',2 union all
    select 9,'str-null','a',null,3 union all
    select 10,'null-str',null,'a',4 union all
    select 100,null,'c2','d2',5 
)
select *
,concat_ws('::',coalesce(value1,'-'),coalesce(value2,'-'),coalesce(cast(value3 as varchar)))
, murmur3(to_utf8(concat_ws('::',coalesce(value1,'-'),coalesce(value2,'-'),coalesce(cast(value3 as varchar))))) as hash1
, xxhash64(to_utf8(concat_ws('::',coalesce(value1,'-'),coalesce(value2,'-'),coalesce(cast(value3 as varchar))))) as hash2
from cte
order by id

PowerBi/PowerQuery: Dealing with errors in Excel files

2023-07-18T11:43:00.003-06:00

When you have errors in your excel file, they sometimes leak through and adding "Table.ReplaceErrorValues" or "Table.RemoveRowsWithErrors" doesnt really work. What I have found is to add the error fix step right after the navigation step that loads the sheet.

In the screenshot below, I have used "Table.RemoveRowsWithErrors" after the Navigation step and it fixed the error.

Applying for US passport in 2023

2023-05-28T09:48:00.003-06:00

We needed a new passport for our daughter as her passport expires in 5 months and 3 weeks from date of travel (country requires 6 months).

Panicking, we emailed our senators and representatives. Got a call from one of them and they advised to call passport phone number and tell them country we were travelling to needs a visa (urgent passport appointment is provided 28 days out for countries needing passport).

Time line

May 5: figured out we needed new passport. Called passport agency, was told to call back 2 weeks prior to travel.

May 6: emailed senators and representatives

May 8: got call back from one of the representatives' staff advising about calling back and telling them that we needed a visa

May 9: called passport agency and got an interview date for May 23 at Colorado office in Aurora. Lucky for us this is a 30 minute drive for us.

May 23: appointment was for 8am. Should have lined up 30 minutes early. Line was long, but efficiently managed. Had flight tickets, birth certificate (as passport was for kid and this is considered a new application and not renewal), paper work about needing visa. The entire appointment lasted less than 60 minutes. Was told to return after 2pm on 25th to pick up passport.

May 25: got passport (took 15 minutes)

Reflections:

1. Everyone we spoke to from the phone staff to the people in the Colorado passport office were extremely helpful, efficient and great to work with.

2. Didn't really need help from senator/representatives, but the help they provided telling us the provision for visa, was the breakthrough we needed.

3. Next time we will apply for passport 12 months prior to expiration, as many countries need 6 months validity on passport for travel.

4. Kids need a ds-11 and you are reapplying for a new passport (I believe until age of 16). Their passports are valid for only 5 years.

AWS CloudFormation error: Properties validation failed for resource LAMBDAXXXX with message: #/Code: expected type: JSONObject, found: String

2023-05-12T16:50:00.002-06:00

During a AWS CF deployment I got the following error:

Properties validation failed for resource LAMBDAXXXX with message: #/Code: expected type: JSONObject, found: String

After struggling for over 4 hours, it turned out that the issue was that the path was not correct in the CF Template!!!

  LAMBDAXXXX:

    Type: "AWS::Lambda::Function"

    Properties:

      FunctionName: LambdaIngestXXXX

      Code: "../lambda/lambda_function_folderpath_xxxx/"

      Handler: "lambda_function.lambda_handler"

      Layers:

        - !ImportValue LAYERPYREQUESTS

        - !ImportValue LAYERAWSWRANGLER

The error: "#/Code: expected type: JSONObject, found: String" it turns out translates to "your path ../lambda/lambda_function_folderpath_xxxx/" is wrong!!!

How I hate CloudFormation!!!

Hope this saves someone else time!

Add an image into a PowerBi report via PowerBi.com/PowerBi Service

2023-05-01T18:24:00.002-06:00

Currently, there is no way to insert an image into a PowerBi report via PowerBi.com (power bi service).

One, workaround though, is to insert a "Blank" button and then set the "Fill" options Background

PowerBi Deep Links (URL query string parameter filtering) is not working

2023-04-28T14:53:00.005-06:00

If you find that your deeplinks into a PowerBi report are not working, please make sure you are using a "?" in front of the "filter parameter.

More info: Filter a report using query string parameters in the URL - Power BI | Microsoft Learn

If you are coming from a Reporting Server environment, the filter url used to have a & in front of it, which will not work.

When testing, one easy way to see if your query parameter is working is to check your filter pane. The filter if correctly picked up will show up in there in italics. The following filters the report by a field named "District" in a table named "District". The table name and column names are case sensitive. The value used for filtering is not. Another tell tale sign that your url is not correct is if the filter gets dropped from the URL once the page loads.

?filter=District/District eq 'FD - 01'

Example of an old Report Server Link which will not work because of the "&": ?rs:embed=true&filter=District/District eq 'FD - 01'

Power BI + Denodo: FailedWithUnknownOrUnsupportedDataSources

2023-04-06T15:33:00.003-06:00

We started getting an error with our Power Bi datasets that were using a Denodo connector. This seems to be happeing only with new files and not existing datasets.

The error we were getting is:

Unable to determine the data source. In order to determine details about custom connector based data sources, Power BI service needs to connect to a gateway, however, no gateway was available/reachable. Details: Static analysis failed in gateway. gatewayObjectId:e884ca97-b4d9-45bc-8560-ad3d2d4eaa56, resultCode:FailedWithUnknownOrUnsupportedDataSources Query contains unsupported function. Function name: Denodo.Contents .

Data source for Query1

Locally the PowerBi files works fine, this error shows up in PowerBi service after you publish the dataset and take a look at the settings for the dataset.

It looks like this might be a bug in PowerBI where when you add a new query that uses the Denodo connector, it is adding an empty list to the arguments and that is causing this is.

Source = Denodo.Contents("MyDenodoSourceName", null, []),

The problem is caused by the []. If you change the line to remove the empty [], then everything works fine!

Source = Denodo.Contents("MyDenodoSourceName", null),

Honda Service Codes

2023-02-11T09:25:00.002-07:00

Recently my car was blinking the maintenance minder B167 code.

Turns out each character has a specific meaning.

Main Honda Service Codes

Code	What it Means
A	Change Oil and Filter
B	Replace the engine oil and oil filter. Perform front and rear brake cleaning and lubrication. Adjust parking brake. Perform in-depth inspection of the brake components.

Honda Service Sub-Codes

Sub-Code	What it Means
0	Perform multi-point inspection.
1	Tires need a rotation service.
2	Replace the cabin and engine air filters.
3	Replace the transmission fluid.
4	Replace spark plugs, timing belt (if equipped) and adjust valves.
5	The engine coolant needs replacing.
6	If equipped, rear differential fluid needs replacing.
7	Replace brake fluid.

Connecting to AWS Athena using JDBC (Dbeaver)

2022-11-01T14:02:00.007-06:00

A quick post on setting up a connection to Athena with OKTA using JDBC. The SQL client I am using is DBeaver, but any other tool could be used.

Figure out your connection string.
replace the highlighted sections with values that are valid for your org.
jdbc:awsathena://AwsRegion=us-west-2;S3OutputLocation=s3://my-athena-results-bucket/results-folder/;AwsCredentialsProviderClass=com.simba.athena.iamsupport.plugin.OktaCredentialsProvider;user={user};password={password};idp_host=mycompany.okta.com;App_ID=0aosd2asadsdsdsd/123;SSL_Insecure=true;
In Dbeaver, click on "New Connection", which will bring up the "Connect to a database" dialog. Search for Athena and click next.
Enter your username & password. For my org, I just had to enter my WindowsNT id without the domain, but it might be different for your org.
Click on the button Driver Settings
Replace the "URL Template" with the value from step 1 on this page (connection string)

Click OK.
Test your connection.

That should make it work!

Creating an ODBC connection is very similar:

PowerBi - Row Level Security behavior - without table filter expressions

2022-09-28T10:28:00.002-06:00

This is a quick post on RLS behavior in a powerbi dataset, where you dont define any table filter expressions.

Basically, when you setup a role and you dont define any table filter DAX expressions, it allows you to control who has access to your dataset. This is useful, when you have multiple datasets in a workspace and you need to reduce the access to a particular group.

Here is an example:

In the above case, people not assigned to the RLS role, will get the following message:

I have found this behavior useful many times, when a dataset gets published, and all of a sudden, someone comes up with a late breaking security requirement and needs the dataset locked down. I quickly add a role and publish the dataset and that allows me to control who can see the data. Also, as people can see the reports, just with an error, it allows them to ask for access to this dataset (instead of completely hiding the dataset from them).

PowerBi - Datasets - Create a report from a template

2022-08-23T17:56:00.000-06:00

Have you wondered how to use the "Create a report" >> "From a template" option from a dataset in PowerBi Service?

If you look at the documentation: Dataset details page - Power BI | Microsoft Docs, it is rather unhelpful:

It turns out that, there is some additional information a little earlier in that page: Dataset details page - Power BI | Microsoft Docs

So it turns out that if you create a report from your dataset and post fix the report name as "(template)", it will use that report as the template for the new report. This is extremely helpful, as you can now create a report with instructions and some sample pages that can help users get started. I LOVE THIS FEATURE!

Note: (2022-8-23): Unfortunately, this does not seem to work when you try and create a report directly (from outside the dataset details page).

The "Auto-create report" option does not work! I really wish this did as currently we provide access to our datasets via PowerBi apps and so users dont have access to the dataset details page. Ideally, when the pick the "auto-create report" option with a dataset, it should use the template associated with the dataset.

Bash script and /bin/bash^M: bad interpreter: No such file or directory

2022-07-27T17:30:00.003-06:00

If you get the error: "Bash script and /bin/bash^M: bad interpreter: No such file or directory", then you are likely trying to run a shell script (.sh) from your windows drive. Most likely, the issue is that your line-endings are CR+LF and not the Unix style LF.

If you need to fix this, the simplest way is to open the file in NotePad++ and change the line-endings (bottom right side of the status bar).

Another simple way to do this is to run the following script that runs regex to replace CR+LF with just LF.

sed -i -e 's/\r$//' myfolder/*.*

Another option, if you are using GIT is to add a .gitattributes file with line-endings set to LF.

* text eol=lf

Please note that if you do this, you may have to first delete all files in your repo (expect .git folder and .gitattributes and then run git reset to get the latest files again).

Finally, another option is to not download the files directly from your command prompt or windows explorer. Instead use your WSL terminal to download the files and they should come down with the correct line endings.

PowerBi PowerQuery - Convert Unix Timestamp to DateTime

2022-07-20T16:55:00.002-06:00

The Unix timestamp represents the number of seconds since 1970-01-01. So to convert it to a DateTime in PowerQuery, use the following code:

= Table.AddColumn(Source, "unixTimeStamp to DateTime", each #datetimezone(1970,1,1,0,0,0,0,0) + #duration(0,0,0,[unixtimestamp]))

Power Bi - Filtering by a list of values

2022-06-07T17:48:00.006-06:00

When you need to filter a Power Bi report by multiple values and your users want to copy & paste the list of values by which they wish the report filtered, there isnt an out of the box way to do it in Power Bi.

I recently stumbled upon an AppSource visual called "Mass Filter" and it does exactly that!

And a quick video that shows you how to use it: Power Bi - Filter by Multiple Comma Separated Values - YouTube

Understanding PowerBi Incremental Refresh with Detect Changes

2022-06-03T23:13:00.005-06:00

Incremental refresh involves partitioning of the data and this concept isnt something that is dwelled upon a lot in the documentation. Because of what fields you use for partitioning can make or break incremental refresh, a lot of times, not understanding the underlying concepts can cause weird behavior later on. Most often the issue, is duplicate records or ghost records. These issues typically surface a months after the incremental refresh is implemented, as the time periods for the archived and incremental refresh periods are measured in multiple months.

Here is some typical account data that one might encounter in a CRM system. I will be using this data to illustrate how Incremental Refresh works.

Id	Name	Created Date	Last Modified Date
1	Microsoft	2022-01-01	2022-01-02
2	Sales Force	2022-02-01	2022-02-02
3	Hitachi	2022-03-01	2022-03-01
4	NCM	2022-04-01	2022-04-05
5	Tesla	2022-05-01	2022-06-03

Picking the partitioning field

Incremental refresh works by storing the data in separate partitions. If you choose "months" then a partition is created for each month. These partitions are important for PowerBi as when data needs to be refreshed, the entire partition's data is reloaded. When a partition is older than the number of months you wish to archive, then that partition is dropped. The partitions are always created using a date column.

The first thing to remember is that one needs to pick a date-time column that does not change (invariant). This means that one cannot use the LastModifiedDate column, which many systems capture, as that will change all the time. Typically, one will end up using the CreatedDate field, as that should never change once the record has been created.

Next you need to create 2 parameters named "RangeStart" and "RangeEnd". These need to be of type Date or Date/Time. You will use these 2 parameters to filter the above table by its CreatedDate. You can use any values you like for the RangeStart and RangeEnd on your computer, while developing your dataset, they will be reset upon the first refresh that is performed in PowerBi.com.

This is what the formula looks like:

= Table.SelectRows(#"Reordered Columns", each [createddate] >= RangeStart and [createddate] < RangeEnd)

The next step is to setup the incremental refresh.

For this example, I am using Archive data set to 6 months and Incrementally refresh data starting 3 months before refresh date. This will make it easier for me to illustrate the way incremental refresh works. You will likely use very different values in your production datasets. Take note of the fact that I have set the "detect data changes" option. This allows you to fine tune the incremental refresh even more (and a setting I think you should definitely use). Instead of refreshing all the partitions in the "Incremental Refresh" period, only those partitions that have changes are refreshed. But how does PowerBi know which partitions changed? You use a field that is updated anytime a record is updated (an audit date column). Typically in most systems this is a field called LastModifiedDate. PowerBi, keeps track of the max value of this field for each Incremental Refresh partition. The next time the dataset is refreshed, only those partitions are reloaded where the source data returns a higher LastModifiedDate than the one PowerBi captured the last time it ran. SalesForce note: you should use the SysModStamp column instead of the LastModifiedDate column, as even backend process changes will update the SysModStamp column, where as LastModifedDate is typically only updated by user based changes.

With the settings as defined above (3 months of incremental refresh and another 3 months of archived data, for a total of 6 months of data), on the very first refresh in PowerBi.com, we will see 3 + (3*2) queries getting fired off (3 for the archived months and 6 for incremental refresh months). The reason you see the additional set of queries for the incremental refresh is that PowerBi will send 2 queries per partition (in this case its by month). These queries will look like this:

select max("systemmodstamp") as "C1" from ( select "systemmodstamp" from account where "createddate" >= timestamp '2022-01-01 00:00:00' and "createddate" < timestamp '2022-02-01 00:00:00' ) as "ITBL"

select "id", "name", "isdeleted", "createddate", "systemmodstamp" from account where "createddate" >= timestamp '2022-01-01 00:00:00' and "createddate" < timestamp '2022-02-01 00:00:00'

The 1st query is on the "detect data changes" column (sysmodstamp) and gets the max value for that partition. The 2nd query gets the data for that partition. After this first refresh in PowerBi.com, all 6 months of data are loaded into the dataset.

Now on every subsequent refresh, PowerBi will first send similar queries as this for each partition that is part of "Incremental Refresh". In our case it will run the queries for the months of April, May, June.

select max("systemmodstamp") as "C1" from ( select "systemmodstamp" from account where "createddate" >= timestamp '2022-04-01 00:00:00' and "createddate" < timestamp '2022-05-01 00:00:00' ) as "ITBL"

PowerBi will then compare these max values to the previous max values it retrieved. PowerBi will only refresh those partitions for which the new max values are different from the previous max values on the detect-changes-column. By doing this, PowerBi can be even more efficient about which partitions it is reloading. If you had not used the detect-data-changes option, then for every subsequent refresh, PowerBi would reload all 3 "incremental refresh" partitions.

Now lets get back to the data we were loading. Initially this data was (I have added the partition each record would be loaded into, in this table). Lets assume this occurred on 6/3/2022

Id	Name	Created Date	Last Modified Date	Partition
1	Microsoft	2022-01-01	2022-01-02	January (archived)
2	Sales Force	2022-02-01	2022-02-02	February (archived)
3	Hitachi	2022-03-01	2022-03-01	March (archived)
4	NCM	2022-04-01	2022-04-05	Apr (Incr. Refresh)
5	Tesla	2022-05-01	2022-06-03	May (Incr. Refresh)

Lets assume the following changes happened on the next day (6/4/2022)

Id	Name	Created Date	Last Modified Date	Partition
1	Microsoft (deleted)	2022-01-01	2022-06-04	January (archived)
2	SalesForce (SFDC)	2022-02-01	2022-02-02	February (archived)
3	Hitachi	2022-03-01	2022-03-01	March (archived)
4	National CineMedia	2022-04-01	2022-06-04	Apr (Incr. Refresh)
5	Tesla/Twitter	2022-05-01	2022-06-04	May (Incr. Refresh)
6	Disney	2022-06-04	2022-06-04	June (Incr. Refresh

In the above case, because rows 1 and 2 are in archived partitions they will not be reloaded. This can be a problem if you support soft-deletes, as now your report potentially has 1 additional record than the source (as row 1 was soft-deleted in the source system). The name change from row 2 will also not show up in your report. The only changes that will be loaded are the changes to the rows 4, 5 and 6.

Finally, when July rolls around, the January partition will be dropped entirely from the dataset and a July Partition will be added.

Id	Name	Created Date	Last Modified Date	Partition
1	~~Microsoft (deleted)~~	~~2022-01-01~~	~~2022-06-04~~	~~January (archived)~~ Partition is dropped
2	SalesForce (SFDC)	2022-02-01	2022-02-02	February (archived)
3	Hitachi	2022-03-01	2022-03-01	March (archived)
4	National CineMedia	2022-04-01	2022-06-04	Apr (archived) Partition type changed
5	Tesla/Twitter	2022-05-01	2022-06-04	May (Incr. Refresh)
6	Disney	2022-06-04	2022-06-04	June (Incr. Refresh
7	Paramount Studios	2022-07-04	2022-07-04	July (Incr. Refresh) Partition is added

Incremental Refresh and CRM data:

When you use "CreatedDate" as your partition field, one thing you have to think about is what period of data you need refreshed. Imagine if you set up your Incremental Refresh to be 12 months and Archive period to be 24 months. On an entity like "Account", sometimes accounts may get created and may not get sold to or touched for a long time. So, if the account was created more than 12 months ago, then when it gets finally used and if any changes were to occur on that entity, you would not see those changes. In the above example, Microsoft is no longer even in the dataset, as its created date is outside the archival range. If Microsoft had an order on 2022-06-04, you would see the order's details, but the order would not have the account details, as its no longer being loaded.

For this reason, one needs to know what is the maximum time period between last-modified on an entity and its created date. You would then have to setup your refresh settings based on the usage statistics of the entities at your company. This is also one of the biggest reasons that it is very hard to setup an incremental refresh with CRM data.

When it comes to Order/Opportunity data, you have a little more lee-way. Typically one should use a field like "Order-Date" and not something like "Shipped-Date", as the latter could change. Also, you could institute a rule that states that orders/opportunities that are older than x months should be closed and a new one created.

What would happen if you use a partition column that might change

Lets take the following data as an example:

Id	Order Name	Order Date	Last Modified Date	Order Value	Partition
1	My Big Order	2021-01-01	2022-01-02	$1 million	January

If we were to have a refresh setting where the partition field were set to "Order Date" and where we incrementally refresh data in the last 12 months and archive another 12 months of data. The above order would have be in an "archived" partition. Now if the Order Date were to be changed to 2022-06-01:

Id	Order Name	Order Date	Last Modified Date	Order Value	Partition
1	My Big Order	2022-06-01	2022-06-01	$1.5 million	January

This same record would be loaded into the "Incremental Refresh" partition. So you would have 2 copies of the same order in your dataset and your order value would be over stated by $1 million. One could try and de-dup this data, but PowerBi is very inefficient at de-duping large record sets. Also, your algorithm would have to be smart, as you would want to keep the latest version of the order.

Important things to remember:

The incremental refresh column must be a date/time column. You can use an integer column but it needs to be of type yyyymmdd (so you cant use any arbitrary integer column).
Hard deletes cannot be handled by incremental refresh. You can use it only if your entity implements soft-deletes (i.e., uses a flag to denote a delete). So if your system performs hard-deletes, then you are SOL!
Make sure that query folding is supported by your data-source and that it is occurring, otherwise, you will end up pulling multiple copies of the data from the data-source and end up considerably slowing down your refresh and possibly inviting the wrath of your data-source administrators. (Don't ask me how I know!)

Some Tips and Notes:

If you implement incremental refresh in your dataset, then know that you will not be able to download your PBIX file from powerbi.com. Plan for this ahead of time. Another important consideration is that if you implement incremental refresh in your dataset and you need to make model changes, then the partitions will be deleted and the entire data reloaded. This is another reason to not implement incremental refresh directly in your dataset.
If you do need to implement incremental refresh in your dataset, then consider:

Storing your PBIX file in a shared location and have an agreed upon method for your team to work on that file. I highly recommend using Sharepoint for this.
You can publish your changes using a tool like "ALM Toolkit" This will not cause your dataset to loose its changes. You can also use the deployment pipeline to push your changes in PowerBi and again this will not delete your existing partitions.

Often times you need to implement incremental refresh because your source is very slow. In this case, you could implement the incremental refresh as a dataflow. You would pull the dataflow into your dataset. In this case, you may not have to implement incremental refresh in your dataset, as the data may load fast enough from the data-flow into your dataset.
PowerBi will setup a partitions based on your incremental refresh settings. When powerbi refreshes data, it is refreshing the entire partition (i.e., it will delete the partition and reload the data from the data-source). So you need to carefully consider the incremental refresh settings and figure out how many rows on average might fall in a partition. If you partition by year and each year has a billion rows, then you might want to consider partitioning by month.