Power Query, Datenquelle für Seriendruck erstellen, Textbausteine in Abhängigkeit von Werten

Erstellung einer Datenquelle für Seriendruck mit Microsoft Word.
Dabei werden Textbausteine in Abhängigkeit von Werten (hier: Kennzahlenwerte) verwendet.

1 Vorbereitung

[Punkte] je Lieferant und Kennzahl in [Tabelle1] eintragen

Wertebereiche ([ab], [bis]) je [Kennzahl] sowie [Textbaustein] in [Tabelle2] definieren

Über Excel Makro [TextID] ermitteln lassen


Excel VBA Code, um [TextID] (Textbaustein) in Abhängigkeit des Wertebereichs, innerhalb dessen sich eine Kennzahl befindet, zu ermitteln und zuzuordnen

--- SCHNIPP ---

Public Sub TextID_zuordnen()

'Text in Abhängigkeit von Kennzahl und Punktzahl zuweisen

Dim wksLiefBew As Worksheet, wksTextID As Worksheet
Dim lZeile As Integer
Dim lTextID As Integer

Set wksLiefBew = ActiveWorkbook.Worksheets("Lieferantenbewertung_Ergebnis")
Set wksTextID = ActiveWorkbook.Worksheets("Texte")

For lZeile = 1 To wksLiefBew.Cells(Rows.Count, 1).End(xlUp).Row

      If wksLiefBew.Range("A" & lZeile).Value <> "" Then
       
          For lTextID = 1 To wksTextID.Cells(Rows.Count, 1).End(xlUp).Row
            If wksLiefBew.Range("B" & lZeile).Value = wksTextID.Range("A" & lTextID).Value _
               And wksLiefBew.Range("C" & lZeile) >= wksTextID.Range("B" & lTextID) _
               And wksLiefBew.Range("C" & lZeile) <= wksTextID.Range("C" & lTextID) Then
               wksLiefBew.Range("D" & lZeile).Value = wksTextID.Range("D" & lTextID).Value
            End If
          Next lTextID
       
      End If

Next lZeile

Set wksLiefBew = Nothing
Set wksTextID = Nothing

End Sub

--- SCHNAPP ---

VBA Code um Power Query zu aktualisieren siehe hier


2 Power Queries erstellen (Arbeitsmappenabfragen)

[Tabelle1] und [Tabelle2] über Feld [TextID] verknüpfen

neue Arbeitsmappenabfrage [Basis_Datenquelle_Seriendruck]

Language M Code

---- SCHNIPP

let
    Quelle = Table.NestedJoin(Lieferantenbewertung_Ergebnis,{"TextID"},Texte,{"TextID"},"Texte",JoinKind.LeftOuter),
    #"Erweiterte Texte" = Table.ExpandTableColumn(Quelle, "Texte", {"Textbaustein"}, {"Texte.Textbaustein"}),
    Sortierung_nach_Lieferant = Table.Sort(#"Erweiterte Texte",{{"Lieferant", Order.Ascending}, {"Kennzahl", Order.Ascending}}),
    #"Entfernte Spalten" = Table.RemoveColumns(Sortierung_nach_Lieferant,{"TextID"})
in
    #"Entfernte Spalten"

---- SCHNAPP ----


2 weitere Arbeitsmappenabfragen auf Basis (Referenz, Verweis) [Basis_Datenquelle_Seriendruck] erstellen

[Variante_Datenquelle_Seriendruck_Teil1]
[Variante_Datenquelle_Seriendruck_Teil2]

---- SCHNIPP [Variante_Datenquelle_Seriendruck_Teil1]

let
    Quelle = Basis_Datenquelle_Seriendruck,
    #"Entfernte Spalten" = Table.RemoveColumns(Quelle,{"Punkte"}),
    #"Pivotierte Spalte" = Table.Pivot(#"Entfernte Spalten", List.Distinct(#"Entfernte Spalten"[Kennzahl]), "Kennzahl", "Texte.Textbaustein"),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Pivotierte Spalte",{{"Liefertreue", "Text_Liefertreue"}, {"Mengentreue", "Text_Mengentreue"}, {"Qualität", "Text_Qualität"}})
in
    #"Umbenannte Spalten"

---- SCHNAPP  [Variante_Datenquelle_Seriendruck_Teil2]

let
    Quelle = Basis_Datenquelle_Seriendruck,
    #"Entfernte Spalten" = Table.RemoveColumns(Quelle,{"Texte.Textbaustein"}),
    #"Pivotierte Spalte" = Table.Pivot(#"Entfernte Spalten", List.Distinct(#"Entfernte Spalten"[Kennzahl]), "Kennzahl", "Punkte"),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Pivotierte Spalte",{{"Liefertreue", "Punkte_Liefertreue"}, {"Mengentreue", "Punkte_Mengentreue"}, {"Qualität", "Punkte_Qualität"}})
in
    #"Umbenannte Spalten"

---- SCHNAPP



Beide Arbeitsmappenabfragen über eine neue Arbeitsmappenabfrage Feld [Lieferant] zusammenführen

[Merge_Variante_Datenquelle_Seriendruck]

---- SCHNIPP [Merge_Variante_Datenquelle_Seriendruck]

let
    Quelle = Table.NestedJoin(Variante_Datenquelle_Seriendruck_Teil1,{"Lieferant"},Variante_Datenquelle_Seriendruck_Teil2,{"Lieferant"},"Variante_Datenquelle_Seriendruck_Teil2",JoinKind.LeftOuter),
    #"Erweiterte Variante_Datenquelle_Seriendruck_Teil2" = Table.ExpandTableColumn(Quelle, "Variante_Datenquelle_Seriendruck_Teil2", {"Punkte_Liefertreue", "Punkte_Mengentreue", "Punkte_Qualität"}, {"Punkte_Liefertreue", "Punkte_Mengentreue", "Punkte_Qualität"})
in
    #"Erweiterte Variante_Datenquelle_Seriendruck_Teil2"

---- SCHNAPP

3 Seriendruck Microsoft Word


[Merge_Variante_Datenquelle_Seriendruck]

Seriendruckfelder in Word Dokument einfügen

Exkurs

benutzerdefinierte Funktion zur Transformation von (Verhältnis- oder absoluten Kennzahlen-) Werten in Punkte.

Voraussetzungen

• Definition überschneidungsfreie Wertebereiche ([ab], [bis])
• Zuweisung Punkte
• je Kennzahl

Language M Code

--- SCHNIPP

(Kennzahl as text, Wert as number) as number =>
let
 Quelle = Excel.CurrentWorkbook(){[Name="tbl_Punkte"]}[Content],
 WertPunkteZuordnung = Table.TransformColumnTypes(Quelle,{{"Kennzahl", type text}, {"ab", type any}, {"bis", type any}, {"Punkte", type any}}),
    IndexHinzufuegen = Table.AddIndexColumn(WertPunkteZuordnung, "Index", 1, 1),
    SelektierteKennzahl = Table.SelectRows(IndexHinzufuegen, each ([Kennzahl] = Kennzahl)),
    ZugeordnetePunkte = Table.SelectRows(SelektierteKennzahl, each [bis] >= Wert and [ab] <= Wert),
    RueckgabePunkte = ZugeordnetePunkte[Punkte]{0}
in
   RueckgabePunkte

--- SCHNAPP