Examples on Dax.Guide have a “try it” button to launch the editor

Examples on Dax.Guide have a “try it” button to launch the editor
culture
converting from text
. TransformColumnTypes
using the optional parameter: Culture
If they were all the same culture, like en-GB
, you can choose column type Using Locale...
which will create a step like this:
= Table.TransformColumnTypes(
Source,
{{"DateString", type datetime}},
"en-GB"
)
cultures
Using a Lookup ColumnRecord.Field( record, key)
lets you lookup a record
field without hard-coding the name. That value is used by DateTime.FromText( string, culture)
let
// These datetimes are all the same time, using a different 'culture'
Source = #table(
type table[DateString = text, User = text],
{
{ "2/13/2021 7:01:46 PM", "Bob" },
{ "13/02/2021 19:01:46", "Jen" },
{ "13.02.2021 19:01:46", "Kate" }
}
),
Culture = [
Bob = "en-US",
Jen = "de-DE",
Kate = "en-GB"
],
// [User] column is mapped to a 'culture'
// then [DateString] is converted
DateTime_FromDynamicCulture = (row as record) as datetime =>
let
author = row[User],
culture = Record.Field( Culture, author),
result = DateTime.FromText( row[DateString], culture)
in
result,
convertDatetimes = Table.AddColumn(
Source, "DateTime_DynamicCulture",
each DateTime_FromDynamicCulture(_),
type datetime
)
in
convertDatetimes
Culture.Current
Get-Culture
in PowerShellusing Get-Culture
Searching for partial matches of english
DateTime.ToText
and Date.ToText
DateTime
format stringsHow to wrap long lines, *without* splitting words. The input is a very long string, with no newlines:
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed eu laoreet turpis. Curabitur lacinia, risus ut rhoncus mattis, turpis lorem iaculis justo, nec ultrices arcu erat vitae felis. Pellentesque vulputate efficitur scelerisque. Etiam bibendum dignissim mauris
List.Accumulate
is an aggregate function. I’m using it to “sum” — to add strings together. If the current line plus the next word is longer than 80 characters, then insert a newline first.
To find the length of the current line, I only want the length after the very last newline. Occurrence.Last
returns the last match, else -1
if nothing is found.
let
LoremIpsum = "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed eu laoreet turpis. Curabitur lacinia, risus ut rhoncus mattis, turpis lorem iaculis justo, nec ultrices arcu erat vitae felis. Pellentesque vulputate efficitur scelerisque. Etiam bibendum dignissim mauris",
// calculate length of string *after* the rightmost newline
Text_LengthAfterNewline = (string as text) as number =>
let
posLastNewline = Text.PositionOf(string, "#(lf)", Occurrence.Last),
posOffset = if posLastNewline <> -1 then posLastNewline else 0,
deltaLen = Text.Length(string) - posOffset
in
deltaLen,
// word wraps text
Text_WordWrap = (string as text, max_width as number) as text =>
let
words = Text.Split(string, " "),
accum_result = List.Accumulate(
words, "",
(state as text, current as text) as text =>
let
len = Text_LengthAfterNewline(state) + Text.Length(current) + 1,
maybeNewline =
if len > max_width then "#(lf)" else "",
accum_string = Text.Combine({state & maybeNewline, current}, " ")
in
accum_string
)
in
accum_result,
wrapped_text = Text_WordWrap(LoremIpsum, 80)
in
wrapped_text
The final result is 80 characters wide or less
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed eu laoreet turpis. Curabitur lacinia, risus ut rhoncus mattis, turpis lorem iaculis justo, nec ultrices arcu erat vitae felis. Pellentesque vulputate efficitur scelerisque. Etiam bibendum dignissim mauris
let
Source = #"wrap lines",
validate_lengths =
let
lines = Text.Split(Source, "#(lf)"),
lengths = List.Transform(
lines,
each [ String = _, Length = Text.Length(_) ])
in
Table.FromRecords(
lengths,
type table[String = text, Length = number],
MissingField.Error )
in
validate_lengths
The main cause of Web.Contents
not refreshing can be fixed by adding the options[Query]
and options[RelativeaPath]
parameters. (The UI doesn’t create them for you)
This example uses https://www.metaweather.com/api/location/search?query=london See more: Skip Test Connection
// lookup city GUID - simplified
let
exampleUrl = "https://www.metaweather.com/api/location/search?query=london",
Headers = [
Accept="application/json"
],
BaseUrl = "https://www.metaweather.com",
Options = [
RelativePath = "/api/location/search",
Headers = Headers,
Query = [
query = "london"
],
ManualStatusHandling = {400, 404}
],
// wrap 'Response' in 'Binary.Buffer' if you are using it multiple times
response = Web.Contents(BaseUrl, Options),
buffered = Binary.Buffer(response),
response_metadata = Value.Metadata(response),
status_code = response_metadata[Response.Status],
final_result =
if List.Contains({400, 404}, status_code) then response_metadata
else buffered,
from_json = Json.Document(final_result),
#"weather record" = from_json{0}
in
#"weather record"
You can filter results based on the status code, instead of getting errors.
See more: override default error handling, and the example wait-for loop
/* web request, act based on the HTTP Status Code returned
see more:
override default error handling: https://docs.microsoft.com/en-us/power-query/handlingstatuscodes
example wait-for loop: https://docs.microsoft.com/en-us/power-query/waitretry#manualstatushandling
*/
let
WikiRequest = (pageName as text) as any =>
let
BaseUrl = "https://en.wikipedia.org/wiki",
Options = [
RelativePath = pageName,
ManualStatusHandling = {400, 404}
],
// wrap 'Response' in 'Binary.Buffer' if you are using it multiple times
response = Web.Contents(BaseUrl, Options),
buffered = Binary.Buffer(response),
response_metadata = Value.Metadata(response),
status_code = response_metadata[Response.Status],
final_result = [
buffered = buffered,
response_metadata = response_metadata
]
in
final_result,
Queries = {"Cat", "DoesNot Exist fake page"},
Items = List.Transform(
Queries,
each WikiRequest( _ )
),
ResponseTable = Table.FromRecords(
Items,
type table[buffered = binary, response_metadata = record], MissingField.Error
),
#"Expanded HTTP Status Codes" = Table.ExpandRecordColumn(ResponseTable, "response_metadata", {"Response.Status"}, {"Response.Status"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded HTTP Status Codes",{{"Response.Status", Int64.Type}})
in
#"Changed Type"
WebRequest
: Wrapper with Better DefaultsYou can get the full file with extra comments: WebRequest.pq
let
/*
Example using this url:
(https://www.metaweather.com/api/location/search?lattlong=36.96,-122.02)
WebRequest(
"https://www.metaweather.com",
"api/location/search",
[ lattlong = "36.96,-122.02" ]
)
Details on preventing "Refresh Errors", using 'Query' and 'RelativePath':
- Not using Query and Relative path cause refresh errors:
(https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/)
- You can opt-in to Skip-Test:
(https://blog.crossjoin.co.uk/2019/04/25/skip-test-connection-power-bi-refresh-failures/)
- Debugging and tracing the HTTP requests
(https://blog.crossjoin.co.uk/2019/11/17/troubleshooting-web-service-refresh-problems-in-power-bi-with-the-power-query-diagnostics-feature/)
*/
WebRequest = (
staticPath as text, // domain
relativePath as text, // basically use everything after ".com" to "?"
optional query as nullable record, // url query string
optional asRaw as nullable logical, // use true if content is not Json
optional headers as nullable record // optional HTTP headers as a record
) as any =>
let
query = query ?? [],
asRaw = asRaw ?? false, // toggles calling Json.Document() or not
headers = headers ?? [
Accept="application/json"
],
baseUrl = staticPath,
options = [
RelativePath = relativePath,
Headers = headers,
Query = query
// optionally toggle handling errors for specific HTTP Status codes
// ManualStatusHandling = {400, 404}
],
// wrap 'Response' in 'Binary.Buffer' if you are using it multiple times
response = Web.Contents(staticPath, options),
metadata = Value.Metadata(response),
buffered = Binary.Buffer(response),
result = Json.Document(buffered)
in
[
response = if asRaw then buffered else result,
status_code = metadata[Response.Status],
metadata = metadata
]
in
WebRequest
let
response_locations = WebRequest(
"https://www.metaweather.com",
"api/location/search",
[ lattlong = "36.96,-122.02" ]
),
location_schema = type table[
distance = number, title = text,
location_type = text, woeid = number, latt_long = text
],
cityListing = Table.FromRecords(response_locations[response], location_schema, MissingField.Error),
city_mergedRequest = Table.AddColumn(
cityListing,
"LocationWeather",
(row as record) as any =>
let
woeid = Text.From(row[woeid]),
response = WebRequest(
"https://www.metaweather.com",
"api/location/" & woeid,
[]
)
in
response,
type any
)
in
city_mergedRequest
CSS Selectors
This fetches the current heading text on the blog
let
Url = "https://powerbi.microsoft.com/en-us/blog/",
Response = Web.Contents( Url ),
/*
note: normally do not pass dynamic urls like this, see cheatsheet on preventing refresh errors
Non-tabular scraping like Images or any single elements, does not use a "RowSelector"
This CSS Selector finds exactly one element, the Page's Header Text
.section-featured-post .text-heading1 a
*/
HeaderText = Html.Table(
Response,
{
{ "Page Header", ".section-featured-post .text-heading1 a" }
}
)
in
HeaderText
let
Url = "https://powerbi.microsoft.com/en-us/blog/",
Response = Web.Contents( Url ),
/*
The 3rd argument in "columnNameSelectorPairs" is the transformation function.
by default it uses:
each _[TextContent]
*/
HeaderAsElement = Html.Table(
Response,
{ { "Link", ".section-featured-post .text-heading1 a", each _ } }
),
ElementRecord = Table.ExpandRecordColumn(
HeaderAsElement, "Link",
{"TagName", "TextContent", "Attributes"}, {"TagName", "TextContent", "Attributes"}
),
ExpandedAttributes = Table.ExpandRecordColumn(
ElementRecord, "Attributes",
{"href", "rel", "title"}, {"attr.href", "attr.rel", "attr.title"}
)
in
ExpandedAttributes
RowSelector
columnNameSelectorPairs
// Docs on Enum
let
Source = "https://docs.microsoft.com/en-us/previous-versions/dynamics/ax-2012/reference/gg841505(v=ax.60)",
// note: normally do not pass dynamic urls like this, see cheatsheet on preventing refresh errors
Response = Web.BrowserContents( Source ),
/*
Think of "RowSelector" as selecting a table row
Then for every row, you select "columns" using the "columnNameSelectorPairs" selector
The combination gives you a table cell.
For more on CSS Selectors, see:
*/
columnNameSelectorPairs = {
// column names don't matter here, since I'm using .PromoteHeaders
{ "Column1", "TABLE.table > * > TR > :nth-child(1)" },
{ "Column2", "TABLE.table > * > TR > :nth-child(2)" },
{ "Column3", "TABLE.table > * > TR > :nth-child(3)" }
},
t1 = Html.Table(
Response, columnNameSelectorPairs,
[RowSelector = "TABLE.table > * > TR"]
),
t2 = Table.PromoteHeaders( t1, [PromoteAllScalars = true] ),
FinalTable = Table.TransformColumnTypes(
t2,
{ { "Name", type text }, { "Value", Int64.Type }, { "Description", type text} }
)
in
FinalTable
Value.NativeQuery()
let
Source = Sql.Database("localhost", "Adventure Works DW"),
Test = Value.NativeQuery(
Source,
"SELECT * FROM DimDate
WHERE EnglishMonthName=@MonthName",
[
MonthName = "March",
DayName = "Tuesday"
]
)
in
Test
let
#"Add Column Pair2" = Table.AddColumn(
Table_Pairs1, "Pairs2",
each Table_Pairs2,
Table.Type
),
#"Expanded Pairs" = Table.ExpandTableColumn(
#"Add Column Pair2",
"Pairs2",
{"Color", "Property"},
{"Color", "Property"}
)
in
#"Expanded Pairs"
Details: https://radacad.com/cartesian-product-in-power-query-multiply-all-sets-of-all-pairs-in-power-bi
JSON
TableTo_Json = (source as table, optional encoding as nullable number) as text =>
let
encoding = if encoding <> null then encoding else TextEncoding.Utf8,
bin = Json.FromValue(source, encoding),
jsonAsText = Text.FromBinary(bin, encoding)
in
jsonAsText
Animals = #table(
{"Animal", "Id"},
{{"Cat", 1}, {"Turtle", 2}} ),
TableTo_Json( Animals )
Json output:
[{"Animal":"Cat","Id":1},{"Animal":"Turtle","Id":2}]
list
of items to a CSV
stringlet
// Converts a list of any type to text. Works well on most types
// although to support all cases, it requires more logic
mixedList = {4, "cat", #date(1999,5,9), 0.4},
ListAsText = List.Transform(mixedList, each Text.From(_)),
CsvText = Text.Combine( ListAsText, ", ")
in
//output: "4, cat, 5/9/1999, 0.4"
CsvText
The Null_coalescing_operator ??
simplifies default values.encoding
will be set to what the user passed, unless it’s null
. In that case, it is set to TextEncoding.Utf8
let
Read_Json = (json as any, encoding as nullable number) as any =>
// calls Json.Document, using UTF8 by default
let
encoding = encoding ?? TextEncoding.Utf8,
result = Json.Document(json, encoding)
in
result
in
Read_Json
Caller chooses which type of conversioin to use, based on type names { date, datetime, datetimezone }
let
// 1] get a `type` from caller
// 2] return a difference function based on the type
GetTransformByType = (_type as type) as function =>
let
// originally from:
func_transform =
if (Type.Is(_type, type date)) then Date.From
else if (Type.Is(_type, type datetime)) then DateTime.From
else if (Type.Is(_type, type datetimezone)) then DateTimeZone.From
else (t) => t // else return self
in
func_transform,
nowDtz = DateTimeZone.LocalNow(),
// invoke in 2 steps
toDate = GetTransformByType(type date),
callDate = toDate( DateTimeZone.FixedLocalNow() ),
// create, and invoke functions
Results = [
asDate = (GetTransformByType(type date))( nowDtz ),
asDateTime = (GetTransformByType(type datetime))( nowDtz ),
asDateTimeZone = (GetTransformByType(type datetimezone))( nowDtz )
]
in
Results
type any
.Table.AddColumn
to set it to number
[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
each
expression in the Lexical Grammareach
functionsFacebook.Graph
(on my machine)#shared
to a table instead of calling Record.FieldNames()
)#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.
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 }
Facebook.Graph
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
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
He covers and links these topics:
Power BI Desktop July 2020 feature summary
date
, time
, datetime
, datetimezone
and duration
primative
typesFacets
Web.Content
‘s Query
and RelativePath
parametersWeb.Contents()
refresh errors all the way back in 2016!advanced editor
check this out.VS Code extension: Power Query uses PowerQuery-Parser and PowerQuery-Formatter
Query Diagnostics in Power BI
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.copy -> paste
multiple queries into at text editor