Excel im betrieblichen Umfeld

  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

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 

  --- 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...

  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...

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" ...

 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")

  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...

 Um ein Data Dictionary in Power BI anzulegen, in Datenmodellierungssicht eine neue Tabelle anlegen und folgenden DAX Code einfügen: --- SCHNIPP --- Data Dictionary =  VAR _columns = SELECTCOLUMNS(     FILTER(         INFO.VIEW.COLUMNS()         , [Table] <> "Data Dictionary" && NOT([IsHidden])     )         , "Type", "Column"         , "Name", [Name]         , "Description", [Description]         , "Location", [Table]         , "Expression", [Expression] ) VAR _measures = SELECTCOLUMNS(     FILTER(         INFO.VIEW.MEASURES()         , [Table] <> "Data Dictionary" && NOT([IsHidden])     )     , "Type", "Measure"     , "Name", [Name]     , "Description", [Description]     , "Location", [Table]...

  neue Abfrage erstellen, Language M Code --- SCHNIPP --- (string as text) as text =>     Text.Combine(         List.Select(             Splitter.SplitTextByAnyDelimiter(                 {" ", ",",";",":"}             )(string),             each List.Count(                 Splitter.SplitTextByEachDelimiter(                     {"@","."}                 )(_)         )=3 and          Text.Length(Text.Select(_, "@"))=1 and         not Text.Contains(_,"@.")     ),"," ) ---SCHNAPP ---

 HTML / Javascript Code zur Konvertierung von Excel in JSON Format --- SCHNIPP --- <!DOCTYPE html> <html lang="en"> <head>     <meta charset="UTF-8" />     <meta name="viewport"            content="width=device-width, initial-scale=1.0" />     <title>Excel to JSON Converter</title>     <style>         body {             font-family: Arial, sans-serif;             margin: 0;             padding: 0;         }         .container {             max-width: 800px;             margin: 50px auto;             padding: 20px;             border: 1px solid #ccc;             border-rad...

  --- SCHNIPP --- (tbl as table, Reihenfolge as list) as table => let     // Quelle: Tabelle oder Datenquelle     Quelle = tbl,     // Liste der gewünschten Spaltenbezeichnungen     GewuenschteSpalten = Reihenfolge,     // Überprüfen, ob alle gewünschten Spalten vorhanden sind     VorhandeneSpalten = List.Intersect({Table.ColumnNames(Quelle), GewuenschteSpalten}),     // Wenn alle gewünschten Spalten vorhanden sind, sortiere sie in der gewünschten Reihenfolge     Ergebnis = if List.Count(VorhandeneSpalten) = List.Count(GewuenschteSpalten) then         Table.ReorderColumns(Quelle, GewuenschteSpalten)     else         Quelle in     Ergebnis

 Excel Office Script 2 Tabellen vergleichen ---- SCHNIPP --- Excel . run ( function   ( context )   {    var  sheet1  =  context . workbook . worksheets . getItem ( "Tabelle1" );    var  sheet2  =  context . workbook . worksheets . getItem ( "Tabelle2" );    var  range1  =  sheet1 . getUsedRange ();   // Ermittelt benutzten Bereich in ersten Tabelle    var  range2  =  sheet2 . getUsedRange ();   // Ermittelt benutzten Bereich in zweiten Tabelle   range1 . load ( "values" );   range2 . load ( "values" );    return  context . sync (). then ( function   ()   {      for   ( var  i  =   0 ;  i  <  range1 . values . length ;  i ++)   {        for   ( var  j...

  über Datentyp Geographie kann Breiten- und Längengrad einer Stadt ermittelt werden fxHaversineDistanz --- SCHNIPP --- let     HaversineDistance = (lat1 as number, lon1 as number, lat2 as number, lon2 as number) =>         let             R = 6371, // Erdradius in km             deg2rad = (deg) => deg * (2 * Number.PI) / 360,             dLat = deg2rad(lat2 - lat1),             dLon = deg2rad(lon2 - lon1),             a = Number.Power(Number.Sin((dLat / 2)),2) + Number.Cos((deg2rad(lat1))) * Number.Cos((deg2rad(lat2))) * Number.Power(Number.Sin((dLon / 2)),2),             c = 2 * Number.Atan2(Number.Sqrt(a), Number.Sqrt(1 - a)),             distance = R * c         in             distance i...

  folgenden Language M Code in eine leere Abfrage (erweiterter Editor) kopieren, Name fxUebersetzen --- SCHNIPP --- (originalText as text, Quellsprache as text, Zielsprache as text) as text =>             // Quellsprache, Zielsprache = ISO Ländercode zB de,en,it,es usw             let                 Quelle = Json.Document(                     Web.Contents("https://translate.googleapis.com/translate_a/single?client=gtx&sl="& Quellsprache &"&tl="& Zielsprache &"&dt=t&q=" & originalText)                 ),                 Uebersetzung = Quelle{0}{0}{0}             in                 Uebersetzung --- SCHNAPP --- Aufruf über Spalte hinzufügen -> benu...

Beispiel als Abfrage in Power Query abbilden neue benutzerdefinierte Spalte hinzufügen: Record.FieldOrDefault(    [     // Datensatz mit Zuordnung           DE  = "Deutschland",  // Abkürzungen Land         ES  = "Spanien",         FR =  "Frankreich",         BE  = "Belgien",         IT =  "Italien"   ],    [Abkürzung], // Nachschlagewert in Datensatz         "nicht vorhanden" // default Wert wenn nicht gefunden )   Ergebnis:

in Power BI Desktop in die Modellansicht wechseln, neue Tabelle -> DAX einfügen:  (hier auf Basis Abfrage (Power Query) Name financials, Feldname [Date], entsprechend anpassen bei Übernahme in eigenes Datenmodell) --- SCHNIPP mit ADDCOLUMNS()  ---  Kalender_DAX =   //VAR StartDatum = EDATE(TODAY(),-48)  //VAR EndDatum = EDATE(TODAY(),48)  //VAR StartDatum =DATE(2013,1,1)  //VAR EndDatum = DATE(2014,12,31)  //oder mit DAX Funktionen FIRSTDATE,LASTDATE   VAR StartDatum = FIRSTDATE(financials[Date])  VAR EndDatum = LASTDATE(financials[Date])    RETURN     ADDCOLUMNS(         CALENDAR(StartDatum,EndDatum),         "Jahr", YEAR([Date]),         "GJ" , YEAR(EDATE([Date],9))-1 & "/" & YEAR(EDATE([Date],9)),         "Jahr Monat", YEAR([Date])  &"."& MONTH([Date]),         "Quartal", QUARTE...

wiederverwendbare Funktion zur Ermittlung von Längen- und Breitengrad auf Basis von Adressdaten neue, leere Abfrage erstellen -> Code kopieren, Funktion umbenennen (zB fxGetLonLat) --- SCHNIPP --- let     GetCoordinates = (address as text) =>     let         // Hier verwenden wir eine Webabfrage, um die Koordinaten zu ermitteln.         // Diese Methode nutzt öffentliche Geodatenquellen.         url = "https://nominatim.openstreetmap.org/search?format=json&q=" & Text.From(address),         response = Web.Contents(url),         json = Json.Document(response),         coordinates = if List.Count(json) > 0 then json{0} else null,         latitude = if coordinates <> null then coordinates[lat] else null,         longitude = if coordinates <> null then coordinates[lon] else null   ...

  DAX Formel für measure: --- SCHNIPP --- SollStd_Gesamt:= SUMX ( SUMMARIZE ( Tabelle2; Tabelle2[Arbeitsplatz]; "SollStd_ErsterWert" ; FIRSTNONBLANK (Tabelle2[SollStd]; 1) ); [SollStd_ErsterWert] ) --- SCHNAPP ---

 Basis Code zum Formatieren von Telefonnummern --- SCHNIPP --- (phoneNumber as text) =>         let             // Entfernen von Leerzeichen aus der Telefonnummer             // zugelassene Zeichen siehe 2ter Parameter Funktion Text.Select()             strippedPhoneNumber = Text.Select(phoneNumber, {"0".."9","(",")","-"}),             // Überprüfen, ob die Telefonnummer mit einer Ländervorwahl beginnt             startsWithPlus = Text.StartsWith(strippedPhoneNumber, "+"),             // Wenn die Telefonnummer mit einer Ländervorwahl beginnt, wird sie zurückgegeben             formattedPhoneNumber = if startsWithPlus then strippedPhoneNumber else "+" & strippedPhoneNumber in     formattedPhoneNumber --- SCHNAPP ---

  --- SCHNIPP --- let     Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],          RemoveBlankColumns =                  let             BlankCols = List.Transform(Table.ToColumns(Quelle), each List.IsEmpty(List.RemoveNulls(_))),             TabCols = Table.ColumnNames(Quelle),             RemoveCols = List.Transform(List.PositionOf(BlankCols, true, Occurrence.All), each TabCols{_})         in              Table.RemoveColumns(Quelle, RemoveCols) in     RemoveBlankColumns ---SCHNAPP ---