Categories
Power BI Power Query

Web.Contents: Using Dynamic and Duplicate key names in a Query

Power BI Discord asked the question:
> How do you use duplicate keys, and dynamic urls with Web.Contents ?
(They were using a web API that required duplicate keys)
After I wrote this, Chris Webb found an easier solution.

Requirements

  1. You can’t use a regular record because keys must be distinct. Query = [ Key1 = 1, Key1 = 10] will throw an error.
  2. You can’t put the **dynamic url** in the first argument Web.Contents or else refreshes can break

I built a RelativePath by Uri-escaping a list.

Building RelativePath by Uri-escaping a list

The final query will request the url: https://www.google.com/search?q=dog&q=cat
This function generates the query string q=dog&q=cat

This Input"q", { "dog", "cat" }
Will Returnq=dog&q=cat
let
    QueryStr_UsingDuplicateKeys = (key as text, values as list) as text =>
    // values are the 'value' of 'key'-> 'value' pairs
        let
            escapedList = List.Transform(
                values,
                each 
                    key & "=" & Uri.EscapeDataString( Text.From(_) )
            ),    
            joinedArgs = Text.Combine(escapedList, "&")
        in
            joinedArgs
in
    QueryStr_UsingDuplicateKeys

Now you can use Web.Contents as normal.

let
    BaseUrl = "https://www.google.com",
    queryStr = QueryStr_UsingDuplicateKeys(
        "q", {"dog", "cat"}
    ),
    Options = [
        RelativePath = "/search?" & queryStr,
        Headers = [ Accept="application/json" ]
    ],
    response_binary = Web.Contents(BaseUrl, Options)
in
    response_binary

Note: BaseUrl is for the static part of the url. Everything else should be in options[RelativePath] or options[Query] See docs: Web.Contents for details.

Easier Solution

First try Chris’s method where you use Query‘s Key-Value pairs a list
Results may vary. If it does not work, you can try this method.
I have not seen Query publicly documented.

Query = [
    q = {"dog", "cat"}
]

Tips from Chris Webb

Using Optional Parameters with Query

Chris has a tip to optionally use null query parameters.
If set to an empty list, {} — the request drops the parameter.
Web.Contents(
    "http://jsonplaceholder.typicode.com/comments",
    [Query = [postId = {}] ]
)

Which results in the url: http://jsonplaceholder.typicode.com/comments
This is a good place to use Power Query‘s null coalesce operator (Which isn’t in the official docs)

Query = [postId = myPostId ?? {}]

Capturing HTTP Requests without Fiddler

The Query Editor is an alternate to using Fiddler to capture web requests.
Categories
Power BI Power Query Quick Tips

Preserving Types When using “Add Custom Column” in Power Query

The default UI sets your column to type any.

You can use the optional argument of Table.AddColumn to set it to number
Or you can declare your function’s return type

Why doesn’t the original [Num] * 2 work?

Powerquery does not know what type will be returned by your function. That’s because each is by definition a function that returns type any

See More

Categories
Excel Power BI Power Query

Which Power Query Functions exist in Power BI but not Excel?

The final number of missing identifiers*

The only function that Power BI is missing is Facebook.Graph (on my machine)

* This query checks for missing identifiers (which may be functions).
( You can filter by type if you convert #shared to a table instead of calling Record.FieldNames() )

Generating the list using #shared

To get a list of all identifiers (functions, variables, constants) I use the variable named #shared . Create a new blank query, then paste this

let
    IdentifierList = List.Sort(Record.FieldNames( #shared ))
in
    IdentifierList

I copy using Copy Entire List, then storing the results into a PowerShell variable. I Repeat the same with Power BI.

That’s more than I expected.

To find out exactly which functions are different, use the Power Shell operator -NotIn

# Find functions in PBI but not Excel
$MissingExcel = $PowerBI | ? { $_  -notin $Excel }

# Find functions in Excel but not PBI
$MissingPowerBI = $Excel | ? { $_  -notin $PowerBI }

The list of functions Power BI is Missing

Facebook.Graph

The list of functions Excel is Missing

Note: This is the list for today, on my machine. Run the #shared query to find any changes.

AI.ExecuteInProc
AI.ExecuteInternal
AI.ExternalSort
AI.GetAutoMLEntity
AI.SampleStratifiedWithHoldout
AI.TestConnection
AIFunctions.Capacities
AIFunctions.Contents
AIFunctions.ExecuteInternal
AIFunctions.GetAutoMLEntity
AIFunctions.PostProcess
AIInsights.Contents
AIInsights.ContentsGenerator
AML.Execute
AML.ExecuteBatch
Acterys.Contents
Actian.Contents
AmazonRedshift.Database
Anaplan.Contents
ApacheHiveLLAP.Database
ApacheSpark.Tables
Asana.Tables
AtScale.Cubes
AutomationAnywhere.Feed
AzureCostManagement.Contents
AzureCostManagement.Tables
AzureDataExplorer.Contents
AzureDataExplorer.Databases
AzureDevOpsServer.AccountContents
AzureDevOpsServer.AnalyticsViews
AzureDevOpsServer.Feed
AzureDevOpsServer.Views
AzureEnterprise.Contents
AzureEnterprise.Tables
AzureHiveLLAP.Database
AzureMLFunctions.Contents
AzureMLFunctions.Execute
AzureMLFunctions.ExecuteBatch
AzureSpark.Tables
AzureTimeSeriesInsights.Contents
BI360.Contents
BIConnector.Contents
Binary.Range
Cdm.Contents
Cdm.MapToEntity
Cds.Contents
Cds.Entities
Cherwell.SavedSearches
Cognite.Contents
CommonDataService.Database
Compression.Brotli
Compression.LZ4
Compression.None
Compression.Snappy
Compression.Zstandard
CustomerInsights.Contents
DataVirtuality.Database
DataWorld.Contents
DataWorld.Dataset
Databricks.Contents
Denodo.Contents
DocumentDB.Contents
Dremio.Databases
Dynamics365BusinessCentral.Contents
Dynamics365BusinessCentral.EnvironmentContents
Dynamics365BusinessCentralOnPremises.Contents
DynamicsNav.Contents
Emigo.Contents
Emigo.GetExtractFunction
EmigoDataSourceConnector.GetExtractFunction
EmigoDataSourceConnector.NavigationFunctionType
EntersoftBusinessSuite.Contents
Essbase.Cubes
Exasol.Database
FactSetAnalytics.AuthenticationCheck
FactSetAnalytics.Functions
Fhir.Contents
Foundry.Contents
Geography.FromWellKnownText
Geography.ToWellKnownText
GeographyPoint.From
Geometry.FromWellKnownText
Geometry.ToWellKnownText
GeometryPoint.From
Github.Contents
Github.PagedTable
Github.Tables
GoogleAnalytics.Accounts
GoogleBigQuery.Database
Graph.Nodes
HexagonSmartApi.ApplySelectList
HexagonSmartApi.ApplyUnitsOfMeasure
HexagonSmartApi.ExecuteParametricFilterOnFilterRecord
HexagonSmartApi.ExecuteParametricFilterOnFilterUrl
HexagonSmartApi.Feed
HexagonSmartApi.GenerateParametricFilterByFilterSourceType
HexagonSmartApi.GetODataMetadata
HexagonSmartApi.Typecast
HiveProtocol.HTTP
HiveProtocol.Standard
HiveProtocol.Type
Html.Table
IRIS.Database
Impala.Database
Indexima.Database
IndustrialAppStore.NavigationTable
InformationGrid.Contents
Intune.Contents
JamfPro.Contents
JethroODBC.Database
Kyligence.Database
Linkar.Contents
LinkedIn.SalesContracts
LinkedIn.SalesContractsWithReportAccess
LinkedIn.SalesNavigator
LinkedIn.SalesNavigatorAnalytics
LinkedIn.SalesNavigatorAnalyticsImpl
List.ConformToPageReader
MailChimp.Collection
MailChimp.Instance
MailChimp.Tables
MailChimp.TablesV2
MariaDB.Contents
MarkLogicODBC.Contents
Marketo.Activities
Marketo.Leads
Marketo.Tables
MicroStrategyDataset.Contents
MicroStrategyDataset.TestConnection
MicrosoftAzureConsumptionInsights.Contents
MicrosoftAzureConsumptionInsights.Tables
MicrosoftAzureConsumptionInsights.Test
MicrosoftGraphSecurity.Contents
Mixpanel.Contents
Mixpanel.Export
Mixpanel.FunnelById
Mixpanel.FunnelByName
Mixpanel.Funnels
Mixpanel.Segmentation
Mixpanel.Tables
Netezza.Database
PQ_Hi_World.Contents
Parquet.Document
Paxata.Contents
Pdf.Tables
PlanviewEnterprise.CallQueryService
PlanviewEnterprise.Feed
PlanviewProjectplace.Contents
PowerBI.Dataflows
PowerPlatform.Dataflows
ProductInsights.Contents
ProductInsights.QueryMetric
Projectplace.Feed
Python.Execute
QubolePresto.Contents
QuickBase.Contents
QuickBooks.Query
QuickBooks.Report
QuickBooks.Tables
QuickBooksOnline.Tables
R.Execute
Roamler.Contents
ShortcutsBI.Contents
Siteimprove.Contents
Smartsheet.Content
Smartsheet.Query
Smartsheet.Tables
Snowflake.Databases
Spark.Tables
SparkPost.GetList
SparkPost.GetTable
SparkPost.NavTable
SparkProtocol.Azure
SparkProtocol.HTTP
SparkProtocol.Standard
SparkProtocol.Type
Spigit.Contents
StarburstPresto.Contents
Stripe.Contents
Stripe.Method
Stripe.Tables
SurveyMonkey.Contents
SweetIQ.Contents
SweetIQ.Tables
Table.AddFuzzyClusterColumn
Table.CombineColumnsToRecord
Table.ConformToPageReader
Table.FuzzyGroup
TeamDesk.Database
TeamDesk.Select
TeamDesk.SelectView
Tenforce.Contents
TibcoTdv.DataSource
TimeSeriesInsights.Contents
Troux.CustomFeed
Troux.Feed
Troux.TestConnection
Twilio.Contents
Twilio.Tables
Twilio.URL
VSTS.AccountContents
VSTS.AnalyticsViews
VSTS.Contents
VSTS.Feed
VSTS.Views
Value.Alternates
Value.Expression
Value.Lineage
Value.Optimize
Value.Traits
Vena.Contents
Vertica.Database
VesselInsight.Contents
Web.BrowserContents
Webtrends.KeyMetrics
Webtrends.Profile
Webtrends.ReportContents
Webtrends.Tables
WebtrendsAnalytics.Tables
Witivio.Contents
WorkforceDimensions.Contents
WorkplaceAnalytics.Data
Zendesk.Collection
Zendesk.Tables
Categories
Power BI Quick Tips

Tip: Controlling a Column’s “Summarize By” Type in Power BI

The Model view allows you to set a global default “Summarize By” type for every column.
You still have the ability to override the aggregation type per-visual in Report view


Categories
Power BI Power Query Reference What's New

Searching Constants Defined in Power Query

Searching Power Query Language Functions and Constants

Categories
Power BI What's New

Power BI Updates – 2020-08-17

Guy In a Cube: July roundup

He covers and links these topics:

PBI Patch Notes Summary

Power BI Desktop July 2020 feature summary

External tools

Tabular Editor

https://tabulareditor.com/

Dax Studio

https://daxstudio.org/

ALM Toolkit

ALM Toolkit

sqlbi.com/alm-toolkit

Categories
Power BI Power Query Reference

Resources to learn Power Query

Tutorials

Ben Gribaudo dives into Power Query topic by topic

Part 1: Introduction to let expressions
Part 3: Using and writing Functions
Part 8: Time: date, time, datetime, datetimezone and duration
Part 11-13: Tables
Part 15: Error handling
Part 16: Power Query primative types
Part 17: Facets
Part 18: Custom types


Chris Webb’s blog: Crossjoin.co.uk
Another high quality resource.
How to use Web API’s through Web.Content‘s Query and RelativePath parameters
He knew how to fix Web.Contents() refresh errors all the way back in 2016!

Documentation

Tools

VS Code extension: Power Query

If you use the advanced editor check this out.
It supports autocomplete Tool tips display optional parameters.

VS Code extension: Power Query uses PowerQuery-Parser and PowerQuery-Formatter

Query Diagnostics

Query Diagnostics in Power BI

Tips

You can copy -> paste multiple queries into a new Power BI Report (notice it included a required referenced query ListAsText even though I didn’t select it.
You can copy -> paste multiple queries into at text editor

Categories
Power BI Power Query

Importing text files in Power BI

What do you do when text files are not formatted as .csv or .json ? If they repeat a pattern, you can automate it.

( You can download the final .PBIX report here )

This file always uses:

  • 3 values for for each “table” row
  • One line per value – which becomes our “columns”
  • every “column” line has a : in it. If you skip all lines without a : it filters all unwanted lines
Title
---------------------
DeviceID: One
Info1: One:Info1
Info2: One:Info2

---------------------

DeviceID: Two
Info1: Two: Info1
Info2: Two: Info2

---------------------

DeviceID: DeviceID
Info1: Three: Info1
Info2: Three: Info2

First take a look at the final function. Then I’ll explain how it works.

The main query calls a custom function

ConvertTableFromTextLines( "c:\data\sample.txt", ":", 3 )

All the work is one here

let  ConvertTableFromText = (filepath as text, splitCharacter as text, linesPerRecord as number, optional encoding as nullable number) as table =>
    let
        TextLines = Lines.FromBinary(
            File.Contents( filepath, null ),
            null,
            null,
            encoding ?? TextEncoding.Utf8
        ),
        TextLineTable = Table.FromColumns(
            { TextLines }, {"Line"}
        ),

        Pairs = Table.SelectRows(
            TextLineTable,
            each Text.Contains( [Line], splitCharacter )
        ),

        SingleRecordAsCols = Table.SplitColumn(
            Pairs,
            "Line",
            Splitter.SplitTextByEachDelimiter(
                { splitCharacter }, QuoteStyle.Csv, false
            ),
            {"Name", "Value"}
        ),

        TotalLines = Table.RowCount( SingleRecordAsCols ) ,
        NumberOfGroups = TotalLines / linesPerRecord,

        ListOfRecords = List.Generate(
            () => 0,
            each _ < NumberOfGroups,
            each _ + 1,
            each (
                Record.FromTable(
                    Table.Range(
                        SingleRecordAsCols,
                        _ * linesPerRecord,
                        linesPerRecord
                    )
                )
            )
        ),
        t = Table.FromList(
            ListOfRecords, Splitter.SplitByNothing(), null, null, ExtraValues.Error
        ),

        columnNameList = Record.FieldNames( ListOfRecords{0} ),

        TableOfRecords = Table.ExpandRecordColumn(
            t, "Column1", columnNameList, columnNameList

        )
    in
        TableOfRecords
in
    ConvertTableFromText

First we load the text file as a list. File.Contents( filepath ) reads all file types. Lines.FromBinary( Contents ) converts it into text. Depending on how the file was saved, you may need to set the encoding

Don’t worry if encoding sounds scary. If text loads with the wrong characters, try the other one. Two good ones to try first are:

TextEncoding.WindowsPower BI defaults to this for windows files
TextEncoding.Utf8Anything on the internet uses UTF8 by default.
Tip: If a file truly is ASCII then UTF8 will work

Next remove any lines that are missing :

Then split each line into two columns:
Transform Column -> Text -> Split -> By Delimiter

I started with these options

I renamed them to Name and Value because they will be converted into records.

To convert multiple lines into a single recordI used List.Generate. You can think of it like a for loop

There are

  • 3 lines per record
  • 9 total lines = the number of lines in the file
  • 3 total groups = 9 total lines / 3 lines per record

We need to loop 3 times. I’ll recreate a loop like this, converted to Power Query

for( $i = 0 ; $i < NumberOfGroups; $i += 1){
    CreateRecord()
}

List.Generate() has 4 arguments. Each one is a function. It uses:

  • initialize sets your starting condition. ( set i to 0 )
  • condition tests if we’ve reached the total number of groups
  • next changes the value of i. We want to add 1
    optional selector function creates the value every loop. This calls CreateRecord() once for every loop
    ListOfRecords = List.Generate(
        () => 0,
        each _ < NumberOfGroups,
        each _ + 1,
        each (
            Record.FromTable( 
                Table.Range(
                    SingleRecordAsCols,
                    _ * linesPerRecord,
                    linesPerRecord
                )
            )
        )
    ),

The next function first grabs rows from the table SingleRecordAsCols. It converts those into a single record

Every loop 3 is added to the offset.

i valueoffsetcount
00 * 3 = 03
11 * 3 = 33
22 * 3 = 63

You end up with a list of records

Choose convert to table

Finally expand records to columns

The final result: