Unpivot multiples tablas

[Anonymous]

Buenas,

A partir de este reporte que tiene esta forma :

debo obtener esto  para todos los años: 2018, 2019, 2020 ... etc.  todos en una sola tabla :

.....

Nota: No se debe considerar el total General.

archivo en excel:

https://docs.google.com/spreadsheets/d/1To4JYDIZxluuU7wxCzEL-P3lKfdFoCWj/edit?usp=sharing&ouid=10141...

[BA_Pete]

Hi @Anonymous ,

In Power Query, create a new blank query and paste this over the default code in Advanced Editor. You can then follow the steps I took to complete this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZfbahw5EIZfpZmrLARWpdKhdDk4gTU42GSdwGJ84Q0mDEw8izNZ9tlymxdL6y9pXJ7T7trjAXdLv6ZbXVLpqypfXU0+vv3t9OTD2fnvw/Tk8vTj9M3Yuzh/P1yeXpwPb94O/ffJ68ndt/n8sM3166sXmNVM7h3Ji01+dvpuOvwxnEzPzqZ1fxz9Ov61T1bhrWArghXRimRFtkKsKEaQs8JaQM2Cau3023LxZfH3rC6BQqTacBA0UjDo0ESf0RDrk3gkpKAq1Saxr00mfcEFfOHk5stscVdH3HiLtTNe9b06oW99Xwcxp5qAR+vr58v7xddn+IV2e+ry/ubu61+L++XtcPHj+5/z2adF/ayupdmFC7ZVy5n7b+hhnXUVHNuEM+zl8OrdYrlY3vwz+6WtCq+05fYl0z4jSSccJ5kPn2/vbu9v5uoFbCBnbQp8EtqGZ6jk1CclqRdIFR5JUT0U9IUQj8JaOT5rxbLWBVsRrIhWJCuyFWJFMUJZ68JaQM2CDdZSdPBBgicL61lSp2V1b0oYFIBDmXWQ1IVFdBbp5+ERa7GdM3btsElr+0HEA32wxYLtqPndEP0LebSHMt+Zj6GDxN2kWKV3q9sqIoxL342ZQYm2muwfd+Pq2kpaihrvRI3xSV1B1k3Klih3WV2R1JOipCWB74KnY5CG7TouafhkJ20l2IpgRbQiWZGtECuKESBtJawF1CzYIC0qMTFX/+Swmfk5VAcKgqngBEqux03AX5L6espr2cwmiK1b1xFMjT49lJaz8PhQPttH20kLCo4aspkbEahgn+aSNsx7SOt5e1vWemCLjH4wcSOhaUqNCGcZ5Lt2MbJU8XBF4AcXFaxGEtwiRwHrSQHweWCRBasLtiJYEa1IVmQrxIpihILVhbWAmgXrYIlWhDg7HhFRUDsVlEpEWkyKlo/Jwc+SEPh9AB0Z0dHnNvlDpWiOqs5f+mjPFr2vuVFz6EHqxaci1mNJk7oFk5bR1P7a47pnWH2UPYiZsEBt1h5HfNNYuV9d2+iSsPouMRpBMCOnIXEsBPF7aJlLHVYQl7w2EjC9OHcUzg4VDv8HZ95y1gVbEayIViQrshViRTFCOevCWkDNgnXOfC44Awz/xCj1CEUHvMbcVpuUUd9nyVXlgKIyOQTVqOktOlQrnErZTGRaXcXGltd/ICatfNTSJ3QCQX3kR7Btuu3pAXMPZkgt9SZYCm7IwShkE/ajP4ejjTQewn8HrSesngZ7mNll5xpwXvMUM8JhTIJQ4AoqboLVY96q+5xL0IyHJhGKxxgQHiKpq2qZeQzo+PjQsYWuC7YiWBGtSFZkK8SKYoRC14W1gJoF69Bxcvnw+7GCLh94zy2MB65VdvPImxXlAb60jdKdRfYzPrSGLudDF3nX1z8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t]),
    repNullStringNull = Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13"}),

    remTopRow = Table.Skip(repNullStringNull,1),
    filterColumn1 = Table.SelectRows(remTopRow, each ([Column1] <> null and [Column1] <> "Total general")),
    addYearRowFlag =
    Table.AddColumn(
        filterColumn1,
        "yearRowFlag",
        each if try Value.Is(Number.From([Column1]), type number) otherwise false then [Column1] else null
    ),
    filterYearRows = Table.SelectRows(addYearRowFlag, each ([yearRowFlag] = null)),
    remYearRowFlag = Table.RemoveColumns(filterYearRows,{"yearRowFlag"}),
    repLimaTipo = Table.ReplaceValue(remYearRowFlag,"LIMA Y CALLAO","Tipo",Replacer.ReplaceText,{"Column1"}),
    splitTable =
    Table.FromList(
        Table.Split(repLimaTipo, 6),
        Splitter.SplitByNothing(),
        null, null, ExtraValues.Error
    ),
    promNestedHeaders =
    Table.TransformColumns(
        splitTable,
        {"Column1", each Table.PromoteHeaders(_, [PromoteAllScalars=true])}
    ),
    unpivNestedCols =
    Table.TransformColumns(
        promNestedHeaders,
        { "Column1", each Table.UnpivotOtherColumns(_, {"Tipo"}, "AnoMes", "Cantidad") }
    ),
    expandNestedTables =
    Table.ExpandTableColumn(unpivNestedCols, "Column1", {"Tipo", "AnoMes", "Cantidad"}, {"Tipo", "AnoMes", "Cantidad"})
in
    expandNestedTables

Pete