SAP Kostenstellenhierarchie mit Power Query in Tabelle umwandeln

Beim Download der Kostenstellenhierarchie (Transaktion ksh3) in SAP R/3 erhält man eine Textdatei. Diese lässt sich nur recht umständlich in eine gut weiterverarbeitende Tabelle umwandeln. Wer dennoch die Kostenstellenhierarchie als Dimensionstabelle in Excel oder Power BI verwenden möchte, kann diese Datei einfach mit Hilfe eines Power Query Codes automatisiert in eine Tabelle umwandeln.

Ausgangssituation

Für das Reporting von Kostenstellendaten aus SAP R/3 wird die aktuelle Kostenstellenhierarchie aus SAP benötigt. Die Hierarchie lässt sich mit der Transaktion ksh3 abfragen. Leider kann der Download nur als Textdatei erfolgen, dessen Struktur sich mit einfachen Excelmitteln, wie zum Beispiel „Text in Spalten“ nicht in eine strukturierte Dimensionstabelle umwandeln lässt. Da die Hierarchie sich permanent ändern kann ist eine manuelle Pflege einer Dimensionstabelle aufwendig und fehleranfällig. Der Fachanwender hat keinen Zugriff auf die tiefergehende SAP Tabellen z.B. mit SE16. Die nachfolgende Anleitung basiert auf Excel kann aber auch in Power BI verwendet werden.

Daten mit Power Query umwandeln

1. Textdatei als Quelle einbinden
Als Erstes wird über Daten -> Daten abrufen und transformieren -> Daten abrufen -> Aus Datei -> Aus Txt/CSV die aus SAP heruntergeladene Textdatei auswählt und importiert. Im Vorschaumenü mit Daten transformieren gelangen wir in den Power Query Editor.

2. Hierarchiestufe ermitteln
Die SAP Textdatei hat eine gewisse Logik bei der Verwendung der Zeichen und Leerzeichen. Der gesamte Power Query Code ist unten in diesem Blogbeitrag abgebildet von daher werden hier nur die Schritte grob erläutert. Sie können den Code einfach in den Erweiterten Editor einfügen und ihren Dateipfad anpassen.

Folgende Schritte werden vollzogen:

  1. Mit Hilfe von benutzerdefinierten Hilfsspalten werden Zeilen die lediglich Leerzeichen oder „|“ enthalten gelöscht.
  2. Aus dem Text werden Kostenstellennummer und Kostenstellenbezeichnung ermittelt.
  3. Mit verschiedenen zusätzlichen Hilfsspalten wird ermittelt, welcher „RowType“ eine Zeile ist, also ob es sich um eine „Metric“ oder „Hierarchy“ Zeile handelt.
  4. Mit Hilfe der „RowType“, Anzahl von „|“,sowie der Anzahl der Zeichen vor der Bezeichnung wird ermittelt, um welche Hierarchiestufe es sich handelt.
  5. Für jede Hierarchiestufe wird eine Spalte mit den dazugehörigen Werten gefüllt.
  6. Die Hilfsspalten werden gelöscht.

3. Daten laden

Sie können die fertige Tabelle wie gewohnt als Verbindung, als Tabelle oder in ein Power Pivot Datenmodell laden.

Mögliche Erweiterungen

  • Die fertige Dimensionstabelle kann entweder in einem PowerPivot Modell oder mit Hilfe von Abfragen zusammenführen in Power Query noch um die Kostenstellenstammdaten (Transaktion ksh3 angereichert werden).
  • Falls die Kostenstellenhierarchie mehr als 3 Hierarchiestufen hat lässt sich der Code relative einfach erweitern.
  • Im praktischen Einsatz hat es sich zudem bewährt, dass man den Zugriff auf die Quelldatei flexibel gestaltet.
    Hier bieten sich 2 Optionen an:
  • In der Exceltabelle wird der Dateipfad und/oder der Dateiname hinterlegt und als Parameter im Power Query Quellcode verwendet.
  • Statt einer einzelnen Datei wird der Import aus einem Ordner verwendet. In diesem Ordner legt man immer den neuesten Download aus dem SAP ab und in Power Query Code wird nur die aktuellste Version verwendet.

Fazit
Mit Power Query können Sie aus dem etwas unförmigen Download aus SAP schnell und einfach eine strukturierte Tabelle erstellen. Diese Methode eignet sich sowohl für ad-hoc Analysen als auch für regelmäßige Reportanforderungen. Als manuellen Prozess bleibt der Download der aktuellen SAP Daten erhalten, die Weiterverarbeitung kann automatisiert werden.

Der Power Query Quellcode

let
Quelle = Csv.Document(File.Contents („C:\Users\Renate\Desktop\ksh3 h1000.txt“),[[Delimiter=“,“, Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
RemoveEmptyColumn = Table.RemoveColumns(Quelle,{„Column2“}),
ChangeColumnName = Table.RenameColumns(RemoveEmptyColumn,{{„Column1“, „Source“}}),
AddTestLength = Table.AddColumn(ChangeColumnName, „TextLenght“, each Text.Length([Source]),Int64.Type),
AddNoOfBlanks = Table.AddColumn(AddTestLength, „NoOfBlanks“, each [TextLenght]-Text.Length( Text.Replace(Text.Lower([Source]),“ „,““))),
AddNoOfVerticalLine = Table.AddColumn(AddNoOfBlanks, „NoOfVerticalLine“, each [TextLenght]-Text.Length( Text.Replace(Text.Lower([Source]),“|“,““))),
AddDeleteColumn = Table.AddColumn(AddNoOfVerticalLine, „DeleteColumn“, each if [TextLenght]-[NoOfBlanks]-[NoOfVerticalLine]=0 then „Yes“ else „No“),
DeleteDeleteColumn = Table.SelectRows(AddDeleteColumn, each ([DeleteColumn] = „No“)),
AddSourceOnlyText = Table.AddColumn(DeleteDeleteColumn, „SourceOnlyText“, each Text.Trim(Text.Replace(Text.Replace([Source],“|“,““),“–„,““))),
SplitSourceInMetricNo_MetricName = Table.SplitColumn(AddSourceOnlyText,“SourceOnlyText“,Splitter.SplitTextByEachDelimiter({“ „}, QuoteStyle.Csv, false),{„MetricNo“, „MetricNameO“}),
TextFormatMetric = Table.TransformColumnTypes(SplitSourceInMetricNo_MetricName,{{„Source“, type text}, {„MetricNo“, type text}, {„MetricNameO“, type text}}),
let
Quelle = Csv.Document(File.Contents („C:\Users\Renate\Desktop\ksh3 h1000.txt“),[[Delimiter=“,“, Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
RemoveEmptyColumn = Table.RemoveColumns(Quelle,{„Column2“}),
ChangeColumnName = Table.RenameColumns(RemoveEmptyColumn,{{„Column1“, „Source“}}),
AddTestLength = Table.AddColumn(ChangeColumnName, „TextLenght“, each Text.Length([Source]),Int64.Type),
AddNoOfBlanks = Table.AddColumn(AddTestLength, „NoOfBlanks“, each [TextLenght]-Text.Length( Text.Replace(Text.Lower([Source]),“ „,““))),
AddNoOfVerticalLine = Table.AddColumn(AddNoOfBlanks, „NoOfVerticalLine“, each [TextLenght]-Text.Length( Text.Replace(Text.Lower([Source]),“|“,““))),
AddDeleteColumn = Table.AddColumn(AddNoOfVerticalLine, „DeleteColumn“, each if [TextLenght]-[NoOfBlanks]-[NoOfVerticalLine]=0 then „Yes“ else „No“),
DeleteDeleteColumn = Table.SelectRows(AddDeleteColumn, each ([DeleteColumn] = „No“)),
AddSourceOnlyText = Table.AddColumn(DeleteDeleteColumn, „SourceOnlyText“, each Text.Trim(Text.Replace(Text.Replace([Source],“|“,““),“–„,““))),
SplitSourceInMetricNo_MetricName = Table.SplitColumn(AddSourceOnlyText,“SourceOnlyText“,Splitter.SplitTextByEachDelimiter({“ „}, QuoteStyle.Csv, false),{„MetricNo“, „MetricNameO“}),
TextFormatMetric = Table.TransformColumnTypes(SplitSourceInMetricNo_MetricName,{{„Source“, type text}, {„MetricNo“, type text}, {„MetricNameO“, type text}}),
RemoveBlanksMetricName = Table.AddColumn(TextFormatMetric, „MetricName“, each Text.Trim([MetricNameO])),
DeleteMetricNameO = Table.RemoveColumns(RemoveBlanksMetricName,{„MetricNameO“}),
AddMetricKey = Table.AddColumn(DeleteMetricNameO, „MetricKey“, each [MetricNo]& “ „&[MetricName], type text),
AddStringLenghtBeforeMetricKey = Table.AddColumn(AddMetricKey, „StringLenghtBeforeMetricKey“, each Text.PositionOf([Source], [MetricNo])+1, type text), AddIndex1 = Table.AddIndexColumn(AddStringLenghtBeforeMetricKey, „Index“, 1, 1),
AddIndex0 = Table.AddIndexColumn(AddIndex1, „Index.1“, 0, 1),
AddDeltaNextStringLenght = Table.AddColumn(AddIndex0, „DeltaNextStringLength“, each try AddIndex0[StringLenghtBeforeMetricKey]{[Index]}-AddIndex0[StringLenghtBeforeMetricKey]{[Index]-1} otherwise 0),
AddDeltaPrevStringLength = Table.AddColumn(AddDeltaNextStringLenght, „DeltaPrevStringLength“, each try AddIndex0[StringLenghtBeforeMetricKey]{[Index.1]}-AddIndex0[StringLenghtBeforeMetricKey]{[Index.1]-1} otherwise 0),
AddRowType = Table.AddColumn(AddDeltaPrevStringLength, „RowType“, each if [Index]=1 then „Hierarchy“ else if [DeltaNextStringLength] > 0 then if [DeltaPrevStringLength]=0 then „Metric“ else „Hierarchy“ else „Metric“ ),
AddHierarchyLevel = Table.AddColumn(AddRowType, „HierarchyLevel“, each if [NoOfVerticalLine]=0 then 1 else if [RowType]=“Metric“ then 3 else if [RowType]=“Hierarchy“ then ([StringLenghtBeforeMetricKey]-4)/4+1 else 1),
AddHierarchy1 = Table.AddColumn(AddHierarchyLevel, „Hierarchy 1“, each if [HierarchyLevel] = 1 then [MetricKey] else null ),
FillHierarchy1 = Table.FillDown(AddHierarchy1,{„Hierarchy 1“}),
AddHierarchy2 = Table.AddColumn(FillHierarchy1, „Hierarchy 2“, each if [HierarchyLevel]=2 then [MetricKey] else null),
FillHierarchy2 = Table.FillDown(AddHierarchy2,{„Hierarchy 2“}),
AddMetric = Table.AddColumn(FillHierarchy2, „Metric“, each if [HierarchyLevel]=3 then [MetricKey] else null),
FilterMetric = Table.SelectRows(AddMetric, each ([Metric] <> null)),
RemoveColumns = Table.RemoveColumns(FilterMetric,{„Source“, „TextLenght“, „NoOfBlanks“, „NoOfVerticalLine“, „DeleteColumn“, „MetricKey“, „StringLenghtBeforeMetricKey“, „Index“, „DeltaNextStringLength“,“Index.1″,“DeltaPrevStringLength“, „RowType“, „HierarchyLevel“}),
ChangeColumnOrder = Table.ReorderColumns(RemoveColumns,{„Hierarchy 1“, „Hierarchy 2″,“Metric“, „MetricNo“, „MetricName“})
in
ChangeColumnOrder