HansPeterPfister
HansPeterPfister
/ dynamically Saving Internet Files with Power BI Desktop.pq
Created
December 24, 2018 14:19
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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
/ Saving Internet Files with Power BI Desktop.pq
Last active
December 24, 2018 14:57
Saving Internet Files with Power BI Desktop.pq
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #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="") |
HansPeterPfister
/ Grouped, cumulative total_en.M
Created
June 4, 2018 18:54
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // 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 |
HansPeterPfister
/ fn_cumulative_Total_en.M
Created
June 4, 2018 18:44
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // 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( |
HansPeterPfister
/ cumulative total_en.M
Last active
June 4, 2018 18:34
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
HansPeterPfister
/ cumulative total_en.M
Created
June 4, 2018 18:28
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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"}), | |
| #" |
HansPeterPfister
/ fn_cumulative_Total.M
Last active
June 4, 2018 14:37
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // 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( |
HansPeterPfister
/ cumulative total.M
Last active
June 4, 2018 14:26
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // 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]) |
HansPeterPfister
/ Grouped, cumulative total.M
Last active
June 4, 2018 14:45
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // 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 |
HansPeterPfister
/ fn_Replace_Chars
Created
June 1, 2018 13:05
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // 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)), |
NewerOlder