[Powerquery] - Petit défi sympa 2

Hello les exceliens,

J'espère que vous allez bien, voici un petit défi tiré de "excel bi" sur linkedin. C'est toujours sympa à faire.

A vos requêtes

@+

Bonjour à tous !

Une approche possible :

let
    Source = Excel.CurrentWorkbook(){[Name = "tSource"]}[Content], 
    Traitt = Table.AddColumn(
        Source, 
        "Résultat attendu", 
        each 
            let
                ts = Splitter.SplitTextByWhitespace()([Sentence])
            in
                Text.Combine(
                    List.Transform(
                        List.Sort(
                            List.Transform(
                                ts, 
                                each {
                                    Text.Remove(_, {"0" .. "9"}), 
                                    Number.From(Text.Select(_, {"0" .. "9"}))
                                }
                            ), 
                            each _{1}
                        ), 
                        each _{0}
                    ), 
                    " "
                )
    )
in
    Traitt

Hello JFL,

Je crois que c'est pas la réponse au bon défi

@+

Bonjour à tous de nouveau !

Je n'avais pas remarqué votre aptitude à poster des challenges.....
C'est chose faite maintenant !

Bonsoir à tous !

Une proposition :

let
    Source = Excel.CurrentWorkbook(){[Name = "Tableau1"]}[Content], 
    LTM = Table.FromRows(
        List.TransformMany(
            Source[Log], 
            (x) => List.Skip(Splitter.SplitTextByWhitespace()(Text.Remove(x, ",")), 3), 
            (x, y) => {y, Text.BetweenDelimiters(x, "[", "]"), Number.From(Text.BetweenDelimiters(x, " ", "h"), "en-US")}
        ), 
        {"People", "Serv", "Hrs"}
    ), 
    Pivot = Table.Pivot(LTM, List.Sort(List.Distinct(LTM[Serv])), "Serv", "Hrs", List.Sum), 
    Total_L = Table.AddColumn(Pivot, "Total", each List.Sum(List.Skip(Record.ToList(_)))), 
    Total_C = Total_L & #table(Table.ColumnNames(Total_L), {{"Total"} & List.Transform(List.Skip(Table.ToColumns(Total_L)), List.Sum)})
in
    Total_C

Bonsoir à tous,

Proposition de béotien.

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    #"Fractionner la colonne par délimiteur" = Table.SplitColumn(Source, "Log", Splitter.SplitTextByDelimiter("] ", QuoteStyle.Csv), {"Log.1", "Log.2"}),
    #"Valeur remplacée" = Table.ReplaceValue(#"Fractionner la colonne par délimiteur","[","",Replacer.ReplaceText,{"Log.1"}),
    #"Fractionner la colonne par délimiteur1" = Table.SplitColumn(#"Valeur remplacée", "Log.2", Splitter.SplitTextByDelimiter(" |", QuoteStyle.Csv), {"Log.2.1", "Log.2.2"}),
    #"Fractionner la colonne par délimiteur2" = Table.SplitColumn(#"Fractionner la colonne par délimiteur1", "Log.2.1", Splitter.SplitTextByDelimiter("h", QuoteStyle.Csv), {"Log.2.1.1", "Log.2.1.2"}),
    #"Valeur remplacée1" = Table.ReplaceValue(#"Fractionner la colonne par délimiteur2",".",",",Replacer.ReplaceText,{"Log.2.1.1"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Valeur remplacée1",{{"Log.2.1.1", type number}}),
    #"Lignes filtrées" = Table.SelectRows(#"Type modifié", each true),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Lignes filtrées",{"Log.2.1.2"}),
    #"Fractionner la colonne par délimiteur3" = Table.SplitColumn(#"Colonnes supprimées", "Log.2.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Log.2.2.1", "Log.2.2.2", "Log.2.2.3"}),
    #"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Fractionner la colonne par délimiteur3", {"ID", "Log.1", "Log.2.1.1"}, "Attribut", "People"),
    #"Lignes groupées" = Table.Group(#"Supprimer le tableau croisé dynamique des autres colonnes", {"Log.1", "People"}, {{"Mt", each List.Sum([Log.2.1.1]), type nullable number}}),
    #"Lignes triées" = Table.Sort(#"Lignes groupées",{{"Log.1", Order.Ascending}, {"People", Order.Ascending}}),
    #"Colonne dynamique" = Table.Pivot(#"Lignes triées", List.Distinct(#"Lignes triées"[Log.1]), "Log.1", "Mt"),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Colonne dynamique", "Total", each List.Sum({[#"DATA-MIG"],[#"UX-DES"],[#"WEB-DEV"]})),
    #"Type modifié1" = Table.TransformColumnTypes(#"Personnalisée ajoutée",{{"Total", type number}})
in
    #"Type modifié1"

Cordialement

Bonjour

autre possibilité de transformation (mais moins directe que le List.TransformMany de JFL)

let    
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    Fractionnement = Table.SplitColumn(Source, "Log", Splitter.SplitTextByEachDelimiter({"[", "] ", " | "}),
        {"Vide", "Service", "Durée", "Personne"}),
    Transformation = Table.TransformColumns(Fractionnement, 
        {{"Durée", each Number.From(Text.BeforeDelimiter(_, "h"), "en-US"), type number}, 
         {"Personne", each Text.Split(_, ", "), type list}}),
    Développement = Table.ExpandListColumn(Transformation, "Personne")
in
    Développement

Je m'arrête à cette étape, je considère que la mise en forme devraient être obtenue par TCD dans Excel ou visuel Matrice dans Power BI.

Stéphane

Hello,

Merci pour vos retours, je suis pas un adepte de de List.TransformMany mais c'est assez fou la puissance de cette fonction, je pense que je vais m'y mettre un peu plus. Ca me permettrait d'optimiser un peu certaines requêtes assez fastidieuses au boulot.

Pareil pour le splitter :

Splitter.SplitTextByWhitespace()(Text.Remove(x, ","))

Pas vraiment l'habitude de l'utiliser vu que j'utilisais le Text.Split(" ") mais le fait de pouvoir rajouter tous les arguments que tu veux sur le traitement du texte avant le split ça évite des étapes non nécessaires du style Table.ReplaceValue

Zebulon, ca marche

Merci pour la proposition raccourcix, méthode ou l'on retire les parties non intéressantes et le Table.ExpandListColumn fait le job sur le reste :)

Merci à vous

Option Explicit

Sub Repartition()
    Dim i As Integer, j As Integer, ligne As Integer, colonne As Integer
    Dim table1() As String, table2() As String, table3() As String, log As String
    Dim duree As Single, cell As Range

    For Each cell In Range("B2:B101")
        table1 = Split(cell, "|")
        table2 = Split(table1(0), "]")
        table2(0) = Right(table2(0), Len(table2(0)) - 1)
        colonne = Len(table2(0)) + 2
        If Len(table2(0)) = 8 Then colonne = 7
        duree = Val(table2(1))
        table3 = Split(table1(1), ",")
        For j = 0 To UBound(table3)
            ligne = Asc(Left(Trim(table3(j)), 1)) - 62
            Cells(ligne, colonne) = Cells(ligne, colonne) + duree
        Next j
    Next
End Sub

Une petite macro de rien du tout...