Skip to content

Instantly share code, notes, and snippets.

HansPeterPfister

Block or report user

Report or block HansPeterPfister

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
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.