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
Category: Power BI
Everything Power BI, Power Query, or related
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
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
VS Code extension: Power Query uses PowerQuery-Parser and PowerQuery-Formatter
Query Diagnostics
Query Diagnostics in Power BI
Tips
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.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: