Skip to content

Instantly share code, notes, and snippets.

HansPeterPfister

View GitHub Profile
View dynamically Saving Internet Files with Power BI Desktop.pq
let
RScript =
"#Define download file #(lf)"
& "URL <- " & """" & SourceURL & SourceFileName & """#(lf)" // URL <- ""https://www.six-swiss-exchange.com/shares/companies/download/issuers_all_en.csv""
& "#Define Destination - use forward slash instead of back slash! #(lf)"
& "mainDir <- " & """" & mainDir & """#(lf)" // mainDir <- ""c:/""
& "subDir <- " & """" & subDir & """#(lf)" // subDir <- ""Power BI/""
& "subDir2 <- " & """" & subDir2 & """#(lf)" // subDir2 <- Securities/""
& "filename <- " & """" & filename & """#(lf)" // filename <- ""issuers.csv""
& "Destination <- paste(mainDir,subDir,subDir2, filename, sep="""")
@HansPeterPfister
HansPeterPfister / Saving Internet Files with Power BI Desktop.pq
Last active Dec 24, 2018
Saving Internet Files with Power BI Desktop.pq
View Saving Internet Files with Power BI Desktop.pq
#Define download file
URL <- "https://www.six-swiss-exchange.com/shares/companies/download/issuers_all_en.csv"
#Define Destination - use forward slash instead of back slash!
mainDir <- "c:/"
subDir <- "Power BI/"
subDir2 <- "Securities"
filename <- "issuers.csv/"
Destination <- paste(mainDir,subDir,subDir2, filename, sep="")
View Grouped, cumulative total_en.M
// Code written by Hans Peter Pfister, www.powerbi-pro.com
let
Source = StockMovements,
Calc_Running_Total = Table.Group(
Source, // table as table
{"Product"}, // key as any
// aggregatedColumns as list
{{"Data", // name of new column to be created
// Function Call
View fn_cumulative_Total_en.M
// Code written by Hans Peter Pfister, www.powerbi-pro.com
let
Source = (Input as table) =>
let
Sorting = Table.Sort(Input,{{"Date", Order.Ascending}}), // Sort table
added_Index = Table.AddIndexColumn(Sorting, "Index", 1, 1), // add Index, base 1
cumulativ_total = Table.AddColumn( // add new column with running total
added_Index, "Total",
each List.Sum(
View cumulative total_en.M
code written by Hans Peter Pfister, www.powerbi-pro.com
let
Source = StockMovements,
Sorting = Table.Sort(Source,{{"Date", Order.Ascending}}),
added_Index = Table.AddIndexColumn(Sorting, "Index", 1, 1),
cumulativ_total = Table.AddColumn(added_Index, "Total", each List.Sum(List.Range(added_Index[Amount],0,[Index]))),
ch_Type = Table.TransformColumnTypes(cumulativ_total,{{"Total", Int64.Type}}),
removed_column = Table.RemoveColumns(ch_Type,{"Index"})
in
removed_column
View cumulative total_en.M
code written by Hans Peter Pfister, www.powerbi-pro.com
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddJNCoMwEIbhu7g2JTMZ/5btBXoAceHClkIJxfsvGjQxsfMVBIWH+OZvHCtLl/Cwpb6qq+vnsbzDu7HVVP/YbfazX3z4Iov4te7qkN7X2T835sQCusZpy13DHeDUlV5jzhrinTuUZasxdzuAqWq40ZqzRJuSBVVhbTnKAjRVwchiqRKjDi5VW47Gcz/rsVQRrTnr9m2iFlVFW3GubgB8ZHuNRTX+eQDVNKMBV6UFnKrxupyw2GK3X1Tm/9mTFVkaAKcsGFoebBg6fQE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t, Amount = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,"Apfel","Apple",Replacer.ReplaceText,{"Product"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Orangen","Oranges",Replacer.ReplaceText,{"Product"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Bananen","Bananas",Replacer.ReplaceText,{"Product"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Birnen","Pears",Replacer.ReplaceText,{"Product"}),
#"
View fn_cumulative_Total.M
// Code written by Hans Peter Pfister, www.powerbi-pro.com
(Input as table) =>
let
Sorting = Table.Sort(Input,{{"Datum", Order.Ascending}}), // Sort table
added_Index = Table.AddIndexColumn(Sorting, "Index", 1, 1), // add Index, base 1
cumulativ_total = Table.AddColumn( // add new column with running total
added_Index, "Saldo",
each List.Sum(
View cumulative total.M
// Code written by Hans Peter Pfister, www.powerbi-pro.com
let
Quelle = Lagerbewegungen,
Sorting = Table.Sort(Quelle,{{"Datum", Order.Ascending}}),
added_Index = Table.AddIndexColumn(Sorting, "Index", 1, 1),
cumulativ_total = Table.AddColumn(
added_Index, "Saldo",
each List.Sum(
List.Range(added_Index[Betrag],0,[Index])
View Grouped, cumulative total.M
// Code written by Hans Peter Pfister, www.powerbi-pro.com
let
Source = Lagerbewegungen,
Calc_Running_Total = Table.Group(
Source, // table as table
{"Produkt"}, // key as any
// aggregatedColumns as list
{{"Daten", // name of new column to be created
// Function Call
(Input as table) as table => // data for function
View fn_Replace_Chars
// code written by Hans Peter Pfister, www.powerbi-pro.com
(text as text) =>
let
// Convert string into list, splitted by char
Table.FromList = Table.FromList(Text.ToList(text), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
// Merge with table "Replacements"
Merge = Table.Buffer(Table.NestedJoin(Table.FromList,{"Column1"},Replacements,{"WrongValue"},"KorrekturLISTE",JoinKind.LeftOuter)),
You can’t perform that action at this time.