View fnGetEuropeanStandardTime.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
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 #3 sum over columns - Imke Feldmann.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
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 #2 sum over columns - Bill Szysz.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
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 #1 sum over columns - Bill Szysz.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
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 fnGetDeutscheFeiertageAuswahlBundesland.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
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 fnImportCSVsWithDifferentStructure.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
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
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 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
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
(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
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
(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
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 | |
/* | |
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