Tuesday, July 18, 2023

PowerBi/PowerQuery: Dealing with errors in Excel files

 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.

Sunday, May 28, 2023

Applying for US passport in 2023

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)


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.

Friday, May 12, 2023

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

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!!!

    Type: "AWS::Lambda::Function"
      FunctionName: LambdaIngestXXXX
      Code: "../lambda/lambda_function_folderpath_xxxx/"
      Handler: "lambda_function.lambda_handler"
        - !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!

Monday, May 01, 2023

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

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

Friday, April 28, 2023

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

 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'

Thursday, April 06, 2023

Power BI + Denodo: FailedWithUnknownOrUnsupportedDataSources

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),

    Saturday, February 11, 2023

    Honda Service Codes

    Recently my car was blinking the maintenance minder B167 code.

    Turns out each character has a specific meaning.

    Main Honda Service Codes

    Tuesday, November 01, 2022

    Connecting to AWS Athena using JDBC (Dbeaver)

    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.

    1. Figure out your connection string.
      replace the highlighted sections with values that are valid for your org.
    2. In Dbeaver, click on "New Connection", which will bring up the "Connect to a database" dialog. Search for Athena and click next.
    3. 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.
    4. Click on the button Driver Settings
    5. Replace the "URL Template" with the value from step 1 on this page (connection string)

      Click OK.
    6. Test your connection.
    That should make it work!

    Creating an ODBC connection is very similar:

    Wednesday, September 28, 2022

    PowerBi - Row Level Security behavior - without table filter expressions

    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).

    Tuesday, August 23, 2022

    PowerBi - Datasets - Create a report from a template

    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.