View fnGetEuropeanStandardTime.pq
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, |
View fnGetDeutscheFeiertageAuswahlBundesland.pq
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"}}), |
View #3 sum over columns - Imke Feldmann.pq
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], |
View #1 sum over columns - Bill Szysz.pq
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], |
View #2 sum over columns - Bill Szysz.pq
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], |
View fnImportCSVsWithDifferentStructure.pq
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==================================== */ |
View fnGetDeutscheFeiertageJeBundesland
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", |
View fnReturnCustError.pq
(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", |
View fnUTC2Local.pq
(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 |
View fnRelativerSpaltenvergleich.pq
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 | |
*/ | |
/* |
NewerOlder