Microsoft 365 Modifier un tableau pour reporting

let
    Source = Excel.CurrentWorkbook(){[Name="Maladies"]}[Content],
    ColName = Table.ColumnNames(Source),
    #"Grouped Rows" = Table.Group(Source, ColName{0}, {{ColName{1}, each let
x = List.Distinct(Table.ToList(Table.SelectColumns(_,ColName{1}))) in Text.Combine(x,"-")}, {"Count", each List.Count(List.Distinct(_))}}),
    ColName2 = List.Transform({1..List.Max(#"Grouped Rows"[Count])}, each "Maladie " & Text.From(_)),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.SelectColumns(#"Grouped Rows",ColName), ColName{1}, Splitter.SplitTextByDelimiter("-"), ColName2)
in
    #"Split Column by Delimiter"

Sub Macro_vs_PQ()

Set dico = CreateObject("Scripting.Dictionary")
Dim t: t = Timer

Application.ScreenUpdating = False


With Sheets("Feuil1")
Dim tablo()
tablo = .Range("A1:B" & .Cells(Rows.Count, 1).End(xlUp).Row).Value

Dim i As Long: For i = LBound(tablo) To UBound(tablo)
dico(tablo(i, 1)) = dico(tablo(i, 1)) & tablo(i, 2) & ","
Next i

.Range("D1").Resize(dico.Count) = Application.Transpose(dico.keys)
.Range("E1").Resize(dico.Count) = Application.Transpose(dico.items)
.Range("E1").Resize(dico.Count).TextToColumns comma:=True

For i = 0 To .Range("D1").CurrentRegion.Columns.Count - 2
.Range("E1").Offset(, i) = "Maladie " & i + 1
Next i

End With

Application.ScreenUpdating = True

 
MsgBox Timer - t
Set dico = Nothing
Erase tablo
End Sub