Skip to content

Instantly share code, notes, and snippets.

View SchreiberLars's full-sized avatar

Lars Schreiber SchreiberLars

View GitHub Profile
let fn = () =>
let
Quelle = Json.Document(Web.Contents("http://worldclockapi.com/api/json/cet/now")),
#"In Tabelle konvertiert" = Record.ToTable(Quelle),
#"Gefilterte Zeilen" = Table.SelectRows(#"In Tabelle konvertiert", each ([Name] = "currentDateTime")),
#"Geänderter Typ" = Table.TransformColumnTypes(#"Gefilterte Zeilen",{{"Value", type datetimezone}}),
Output = Table.TransformColumnTypes(#"Geänderter Typ",{{"Value", type datetime}})[Value]{0}
in
Output,
let
/*
This is Bill's version of getting a list of months...
as this is not a critical part for the over all solution,
I just copied it into this solution as well
*/
List_AllMonths = List.Transform({0..11}, each Date.ToText(#date(1900, 1, 1) +
Duration.From(_ * 31), "MMMM", "en-Us")),
Source = Excel.CurrentWorkbook(){[Name="Datenbasis"]}[Content],
let
/*
Creating a list of monthnames... format and language of the list can
easily be changed by the last two parameters
*/
MonthsList = List.Transform({0..11}, each Date.ToText(#date(1900,1,1) +
Duration.From(_*31), "MMMM", "en-GB")),
Source = Excel.CurrentWorkbook(){[Name="Datenbasis"]}[Content],
let
/*
Creating a list of monthnames...
format and language of the list can easily by changes by the last two parameters
*/
MonthsList = List.Transform(
{0..11},
each Date.ToText(#date(1900, 1, 1) + Duration.From(_ * 31), "MMMM", "en-Us")
),
Source = Excel.CurrentWorkbook(){[Name = "Datenbasis"]}[Content],
let fn = (Kalenderjahr as list, Bundesland as text) as list =>
let
//Bundesland = "berlin",
//Kalenderjahr = {2018, 2019},
//Kalenderjahr = 2019,
#"In Tabelle konvertiert" = Table.FromList(if Kalenderjahr is list then Kalenderjahr else {Kalenderjahr}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Geänderter Typ1" = Table.TransformColumnTypes(#"In Tabelle konvertiert",{{"Column1", Int64.Type}}),
#"Umbenannte Spalten" = Table.RenameColumns(#"Geänderter Typ1",{{"Column1", "Jahre"}}),
let func = (PfadZumOrdner as text, Spaltentrennzeichen as text, EncodingCode as number ) as table =>
let
//Hier den Funktions-Corpus eintragen
//=========================================Beispielparameter fürs Debugging====================================
/*PfadZumOrdner = "H:\SSBI-Blog\Website\Business Topics\_csvs mit verschiedenen Headern einlesen\Datenquelle",
Spaltentrennzeichen = ";",
EncodingCode = 1252
*/
//=========================================Beispielparameter fürs Debugging==================================== */
let func = (Kalenderjahr as number) as table =>
let
//Liste aller Bundeländer für den Aufbau der URL bei arbeitstage.org
List_Bundeslaender = {
"baden-wuerttemberg",
"bayern", "berlin",
"brandenburg",
"bremen",
"hamburg",
"hessen",
(IDCustErr as number)=>
let
//IDCustErr = 5, /* for debugging purposes */
// ============ START: Define your list of custom error records here ====================
ListCustomErrors = {
//Custom error #1: The only permissible tax rates are as follows 7% and 19%
[
CustErrID = 1,
reason ="Illegal tax rate",
(DateTimeUTC as datetime) as datetime =>
let
/*
================================================================================
Wesentliche Bestandteile der Funktion basieren auf der Arbeit von 'Frank Tonsen'
================================================================================
*/
Year = Date.Year(DateTimeUTC),
//Ermittlung der Grenze der Winterzeit --> letzter Sonntags im März, 00:59:59 Uhr UTC
let
/*
Beginn der Funktionsdefinition, mit Funktionsnamen, Funktionsparametern und Typ des Funktionsrückgabewertes
*/
fnInsertDiffColumn = (ColumnName as text, MyTable as table) as table =>
let
/*
Parameter für das Testen der Funktion, während der Entwicklungsphase
*/
/*