Transformar datos

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZE9C4MwEIb/imS22ORMNKNfiJSqNHYSpxpEaLWo9Pc3Kg61lFY6HOSG98lzd3mOwiBOjpEnkI4yccBg2RQ4Zlz1UaOlXVt1su9V56jC1ACD7Amot9fe7lc5yBIV+gaMq0obcZehfsgpKwKVTGL/7GXJaSEQk1oMKKMvX80a1MBk1CBvGptQ7jzRJhZwTsECE/7X+oT6ouX4gYjCeIEQmzETU5gWvVvda7XoX6PLjVLZlHVToaJ4Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Vendor = _t, #"Task ID" = _t, #"Task Status" = _t, Team = _t, #"Action Date" = _t, #" Node Status" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Vendor", "Task ID", "Task Status"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
    #"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Index],each if Number.Mod([Index],6)<3 then 1 else 2,Replacer.ReplaceValue,{"Index"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Attribute],each Text.Trim([Attribute] & " " & Text.From([Index])),Replacer.ReplaceValue,{"Attribute"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Replaced Value1",{"Vendor", "Task ID", "Task Status", "Attribute", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Cómo usar este código: Crear una nueva consulta en blanco. Haga clic en "Editor avanzado". Reemplace el código en la ventana con el código proporcionado aquí. Haga clic en "Listo".

Nota: los datos de muestra eran sucios con muchos espacios iniciales y finales, e inconsistencias de nomenclatura. Power Query es muy exigente al respecto. Mi enfoque solo funciona con datos limpios.

Hola

Este código M funciona

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Vendor", "Task ID", "Task Status","Index"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Task ID", "Attribute"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Vendor", "Task Status", "Index", "Value", "Index1"}, {"Vendor", "Task Status", "Index", "Value", "Index1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Sorted Rows", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Task ID", "Merged.2"}),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Split Column by Delimiter", {{"Index1", type text}}, "en-IN"),{"Index1", "Merged.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns1",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Merged]), "Merged", "Value"),
    #"Changed to Date type" = Table.TransformColumnTypes(#"Pivoted Column",List.Transform(List.Select(Table.ColumnNames(#"Pivoted Column"),each Text.EndsWith(_,"Date")), each {_, type date}))
in
    #"Changed to Date type"

Espero que esto ayude.