Posts

Empfohlener Beitrag

nützliche Text Funktionen in Power Query

Bild
Im Folgenden werden ein paar in der Praxis nützliche Text Funktionen in Power Query aufgelistet. Im Voraus soll darauf hingewiesen werden, dass es 2 wesentliche Unterschiede zwischen Excel und Power Query Formeln / Funktionen gibt: case sensitivity Excel Formel unterscheiden nicht zwischen Groß- und Kleinschreibung, Power Query Formeln indes schon. Wenn eine Power Query Signatur Text.Range vorgibt, dann wird TEXT.RANGE oder text.range nicht funktionieren (case sensitive). Basis 1 versus Basis 0 Excel Formeln / Funktionen beziehen sich immer auf die Basis 1, d.h. man fängt mit 1 an zu zählen. Auf der anderen Seite startet das Zählen in einer Power Query Funktion immer mit 0, nicht 1. Vergleich Excel Text mit Power Query Funktionen Text.Contains(Text,Suchstring) gibt TRUE zurück, wenn <Suchstring> in <Text> beinhaltet ist, andernfalls FALSE z.B. Text.Contains("Power Query","Query") Rückgabewert = TRUE Text.Remove([Column],{...
Kommentar veröffentlichen
Mehr anzeigen

Power BI DAX, Rang / laufende Summe mit nicht numerischen Werten

Bild
  Country Rang =  RANKX( ALL(financials[Country]), [Umsatz],, DESC,Dense ) Country laufende Summe =  var _RangLand = RANKX( ALL(financials[Country]), [Umsatz],, DESC,Dense ) var _laufendeSumme = CALCULATE( [Umsatz], Filter(     ALL(financials[Country]),     _rangLand >= RANKX( ALL(financials[Country]), [Umsatz],, DESC,Dense ) ) ) RETURN _laufendeSumme
Kommentar veröffentlichen
Mehr anzeigen

Power Query, auf nicht druckbare Zeichen prüfen

let     CheckInvisibleCharacters = (inputText as text) as logical =>     let         invisiblePatterns = {             "#(cr)",      // Carriage Return             "#(lf)",      // Line Feed             "#(cr)#(lf)", // Carriage Return + Line Feed             "#(tab)",     // Tab             Character.FromNumber(160),  // Non-breaking Space             Character.FromNumber(8203) // Zero-width Space         },         result = List.AnyTrue(List.Transform(invisiblePatterns, each Text.Contains(inputText, _)))     in         result in     CheckInvisibleCharacters 
Kommentar veröffentlichen
Mehr anzeigen

Power Query, Leere Spalten entfernen

Bild
  --- SCHNIPP --- let      Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],     EntferneLeereSpalten =          let             LeereSpalten = List.Transform(Table.ToColumns(Quelle), each List.IsEmpty(List.RemoveNulls(_))),             TabellenSpalten = Table.ColumnNames(Quelle),             EntferneSpalten = List.Transform(List.PositionOf(LeereSpalten, true, Occurrence.All), each TabellenSpalten{_})         in               Table.RemoveColumns(Quelle, EntferneSpalten) in       EntferneLeereSpalten --- SCHNAPP --- --- SCHNIPP --- (tblInput as table) as table => let      Quelle = tblInput,     LeereSpalten = List.Transform(Table.ToColumns(Quelle), each List.IsEmpty(List.RemoveNulls(_))),     TabellenSpalten = Table.C...
Kommentar veröffentlichen
Mehr anzeigen

Power BI; DAX; OFFSET Methode; Geschäftsjahr

Bild
  Measure VorGJ Offset ALLSELECTED = CALCULATE([Umsatz],OFFSET(-1,ALLSELECTED(Kalender_GJ_DAX[GJ]),ORDERBY(Kalender_GJ_DAX[GJ],ASC))) Kalender_GJ_DAX --- SCHNIPP --- Kalender_GJ_DAX =  VAR FirstFiscalMonth = 4 -- Erster Monat des Geschäftsjahres GJ VAR FirstDayOfWeek = 1   -- 0 = Sonntag, 1 = Montag, ... VAR FirstYear =          -- setzt das erste Jahr     YEAR ( MIN ( financials[Date]  )) VAR ErstesDatum =      DATE(YEAR(MIN(financials[Date])),4,1) RETURN GENERATE (     FILTER (         CALENDARAUTO (),         [Date] >= ErstesDatum     ),     VAR Yr = YEAR ( [Date] )            -- Jahr Nummer     VAR Mn = MONTH ( [Date] )           -- Monat Nummer (1-12)     VAR Qr = QUARTER ( [Date] )         -- Quartal Nummer (1-4)     VAR M...
Kommentar veröffentlichen
Mehr anzeigen

Power Query, HTML Sonderzeichen ersetzen

neue Abfrage erstellen, Name fxReplaceHTMLEntities --- SCHNIPP ---  (inputText as text) as text =>     let         // Liste der zu ersetzenden HTML-Sonderzeichen und deren Entsprechungen         HtmlEntities = [             #"%20" = " ",             #"%21" = "!",             #"%22" = """",             #"%23" = "#",             #"%24" = "$",             #"%25" = "%",             #"%26" = "&",             #"%27" = "'",             #"%28" = "(",             #"%29" = ")",             #"%2A" = "*",             #"%2B" = "+",             #"%2C" ...
Kommentar veröffentlichen
Mehr anzeigen

reguläre Ausdrücke; REGEXEXTRAHIEREN;REGEXERSETZEN

 e-mail Adressen aus Text extrahieren =REGEXEXTRAHIEREN(A2;"\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}\b") 0er in Text ersetzen (zB 000020002888 -> 20002888) =REGEXERSETZEN(A5;"^0+(?!$)";"") Sonderzeichen in Text ersetzen (zB re*port -> report) =REGEXERSETZEN(A8;"[^a-zA-Z0-9  ]";"") Datum aus Text extrahieren (Format Jahr-Monat-Tag) =REGEXEXTRAHIEREN(A13;"(\d{4})-(\d{1,2})-(\d{1,2})";2) Telefonnummer mit Länderkennzeichen (zB +49 (0)7999-25080) =REGEXERSETZEN(A16; "^0(\d+)-(\d+)$"; "49 (0) $1-$2")
Kommentar veröffentlichen
Mehr anzeigen

Power Query, prüfen ob Excel Datei vorhanden ist, andernfalls Fehlermeldung ausgeben anstatt Daten

  Parameter  Dateipfad zB D:\Projekte\Microsoft_Power_BI\Test\2021_01.xlsx InputSheet, zB Tabelle1 oder Sheet1 InputKind = zB Sheet fxDateiVorhanden ---- SCHNIPP --- let     Quelle = (InputDateiPfad as text, InputSheet as text, InputKind as text) =>  let     // Pfad zur Datei definieren     DateiPfad = InputDateiPfad,     // Versuche, die Datei zu laden     DateiVersuch = try Excel.Workbook(File.Contents(DateiPfad), null, true),     // Bedingte Logik basierend auf dem Ergebnis des Versuchs     Ergebnis = if DateiVersuch[HasError] then          Table.FromRecords({[Nachricht = "Datei konnte nicht geladen werden. Bitte überprüfen Sie den Speicherort: " & DateiPfad]})     else          let             SheetData = try DateiVersuch[Value]{[Item=InputSheet, Kind=InputKind]}[Data]         in...
Kommentar veröffentlichen
Mehr anzeigen