
Tag: Power BI
Power BI Updates – 2020-08-17
Guy In a Cube: July roundup
He covers and links these topics:
- Extracting data from PDF files
- Calculation Groups
- Sync Slicers: hatfullofdata.blog/power-bi-introducing-sync-slicers
- Patrick uses slicers to control what’s in a report page tooltip
- Slicers that span pages
PBI Patch Notes Summary
Power BI Desktop July 2020 feature summary
- External tools
- Gradient Legend
- Azure Maps Visual (Preview)
- 49 new Excel Financial functions in DAX
- option to globally disable automatic type detection
External tools
Tabular Editor
Dax Studio
ALM Toolkit

Tutorials
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
typesPart 17:
Facets
Part 18: Custom types
Chris Webb’s blog: Crossjoin.co.uk
Another high quality resource.
Web.Content
‘s Query
and RelativePath
parametersWeb.Contents()
refresh errors all the way back in 2016!Documentation
- Power BI docs: top level
- Power Query: top level
- Power Query Functions: top level reference
- Power Query Language Specification
Tools
VS Code extension: Power Query

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

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 editorImporting 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.Windows | Power BI defaults to this for windows files |
TextEncoding.Utf8 | Anything on the internet uses UTF8 by default. |
ASCII
then UTF8
will workNext 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 record
I 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. ( seti
to0
)condition
tests if we’ve reached the total number of groupsnext
changes the value ofi
. We want to add1
optionalselector
function creates the value every loop. This callsCreateRecord()
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 value | offset | count |
0 | 0 * 3 = 0 | 3 |
1 | 1 * 3 = 3 | 3 |
2 | 2 * 3 = 6 | 3 |
You end up with a list
of records

Finally expand records to columns

The final result:
