Skip to content

Instantly share code, notes, and snippets.

Avatar

Lars Schreiber SchreiberLars

View GitHub Profile
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 #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 #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 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 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
*/
/*
View fnGeschaeftsHalbjahr.pq
let
fn = (MyDate as date, StartMonat as text) =>
let
Startmonatsnummer =
List.PositionOf(
{"januar", "februar","märz","april","mai","juni", "juli", "august", "september", "oktober", "november", "dezember"},
Text.Lower(StartMonat)
) + 1,
Offset = Startmonatsnummer -1,
AtuellerKalendermonat = Date.Month(MyDate),
You can’t perform that action at this time.