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

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 editorWhat 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:
:
in it. If you skip all lines without a :
it filters all unwanted linesTitle
---------------------
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
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 groupsnext
changes the value of i
. We want to add 1
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 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:
Current name | Previous names |
---|---|
Power Automate | Flow |
Power Query | M |
Powershell | Powershell Core (this is opensource and uses .net core) |
Windows Powershell | Powershell (this uses .net ) |
Azure Data Studio | SQL Operations Studio |