Pruebe con esto (escriba el código en una consulta en blanco y apunte la fuente a su archivo obviamente)
dejar
Source = Excel.Workbook(File.Contents("C:UsersPaulOneDrive1 Shared webComm PBIsPQ Tablevolumenes-regionales_Tipo de motor x pais y segmento.xlsx"), null, true),
Sheet0_Sheet = Source{[Item="Sheet0",Kind="Sheet"]}[Data],
#"Filas filtradas" = Table.SelectRows(Sheet0_Sheet, cada una ([Columna1] <> null)),
#"Encabezados promocionados" = Table.PromoteHeaders(#"Filas filtradas", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Encabezados promocionados",{{"a", escriba texto}, {"a_1", escriba texto}, {"Argentina", escriba cualquiera}, {"Argentina_2", escriba cualquiera}, {"Argentina_3", escriba cualquiera}, {"Argentina_4", escriba cualquiera}, {"Argentina_5", escriba cualquiera}, {"Argentina_6", escriba cualquiera}, {"Argentina_7", escriba cualquiera}, {"Argentina_8", escriba cualquiera}, {"Argentina_9", escriba cualquiera}, {"Argentina_10", escriba cualquiera}, {"Argentina_11", escriba cualquier}, {"Argentina_12", escriba any}, {"Argentina_13", escriba any}, {"Argentina_14", escriba any}, {"Argentina_15", escriba any}, {"Argentina_16", type any}, {"Argentina_17", type any}, {"Argentina_18", type any}, {"Argentina_19", type any}, {"Argentina_20", type any}, {"Argentina_21", type any}, {"Argentina_22", type any}, {"Argentina_23", type any}, {"Argentina_24", type any}, {"Argentina_25", type any}, {"Argentina_26", type any}, {"Argentina_27", type any}, {"Argentina_28", type any}, {"Argentina_29", type any}, {"Argentina_30", type any}, {"Argentina_31", type any}, {"Argentina_32", type any}, {"Bolivia", type any}, {"Bolivia_33", type any}, {"Bolivia_34", type any}, {"Bolivia_35", type any}, {"Bolivia_36", type any}, {"Bolivia_37", type any}, {"Bolivia_38", type any}, {"Bolivia_39", type any}, {"Bolivia_40", escriba any}, {"Bolivia_41", escriba any}, {"Brasil", type any}, {"Brasil_42", type any}, {"Brasil_43", type any}, {"Brasil_44", type any}, {"Brasil_45", type any}, {"Brasil_46", type any}, {"Brasil_47", type any}, {"Brasil_48", type any}, {"Brasil_49", type any}, {"Brasil_50", type any}, {"Brasil_51", type any}, {"Brasil_52", type any}, {"Brasil_53", type any}, {"Brasil_54", type any}, {"Brasil_55", type any}, {"Brasil_56", type any}, {"Brasil_57", type any}, {"Brasil_58", type any}, {"Brasil_59", type any}, {"Brasil_60", type any}, {"Brasil_61", type any}, {"Chile", type any}, {"Chile_62", type any}, {"Chile_63", type any}, {"Chile_64", type any}, {"Chile_65", type any}, {"Chile_66", type any}, {"Chile_67", escriba any}, {"Chile_68", escriba any}, {"Chile_69", escriba any}, {"Chile_70", escriba any}, {"Chile_71", escriba any}, {"Chile_72", type any}, {"Chile_73", type any}, {"Chile_74", type any}, {"Chile_75", type any}, {"Chile_76", type any}, {"Chile_77", type any}, {"Chile_78", type any}, {"Chile_79", type any}, {"Chile_80", type any}, {"Chile_81", escriba any}, {"Chile_82", escriba any}, {"Chile_83", escriba any}, {"Chile_84", escriba any}, {"Chile_85", escriba any}, {"Chile_86", type any}, {"Chile_87", type any}, {"Chile_88", type any}, {"Chile_89", type any}, {"Chile_90", type any}, {"Chile_91", type any}, {"Chile_92", type any}, {"Colombia", type any}, {"Colombia_93", type any}, {"Colombia_94", escriba any}, {"Colombia_95", escriba any}, {"Colombia_96", type any}, {"Colombia_97", type any}, {"Colombia_98", type any}, {"Colombia_99", type any}, {"Colombia_100", type any}, {"Colombia_101", type any}, {"Colombia_102", type any}, {"Colombia_103", type any}, {"Colombia_104", type any}, {"Colombia_105", type any}, {"Colombia_106", type any}, {"Colombia_107", type any}, {"Colombia_108", escriba any}, {"Colombia_109", escriba any}, {"Colombia_110", escriba any}, {"Colombia_111", escriba any}, {"Colombia_112", escriba any}, {"Colombia_113", escriba any}, {"Colombia_114", escriba any}, {"Colombia_115", escriba any}, {"Colombia_116", escriba any}, {"Colombia_117", escriba any}, {"Colombia_118", escriba any}, {"Colombia_119", escriba any}, {"Colombia_120", escriba any}, {"Colombia_121", escriba any}, {"Colombia_122", tipo cualquiera}, {"Colombia_123", escriba cualquiera}, {"Costa Rica", escriba cualquiera}, {"Costa Rica_124", escriba cualquiera}, {"Costa Rica_125", escriba cualquiera}, {"Costa Rica_126", escriba cualquiera}, {"Costa Rica_127", escriba cualquiera}, {"Costa Rica_128", escriba cualquiera}, {"Costa Rica_129", escriba cualquiera}, {"Costa Rica_130", escriba cualquiera}, {"Costa Rica_131", escriba cualquiera}, {"Costa Rica_132", escriba cualquiera}, {"Costa Rica_133", escriba cualquiera}, {"Costa Rica_134", escriba cualquiera}, {"Costa Rica_135", escriba cualquiera}, {"Costa Rica_136", escriba cualquiera}, {"Costa Rica_137", escriba cualquiera}, {"Costa Rica_138", escriba cualquiera}, {"Costa Rica_139", escriba cualquiera}, {"Costa Rica_140", escriba cualquiera}, {"Costa Rica_141", escriba cualquiera}, {"Costa Rica_142", escriba cualquiera}, {"Costa Rica_143", escriba cualquiera}, {"Costa Rica_144", escriba cualquiera}, {"Costa Rica_145", escriba any}, {"Costa Rica_146", type any}, {"Costa Rica_147", type any}, {"Costa Rica_148", type any}, {"Costa Rica_149", type any}, {"Costa Rica_150", type any}, {"Costa Rica_151", type any}, {"Costa Rica_152", type any}, {"Costa Rica_153", type any}, {"Costa Rica_154", type any}, {"Ecuador", type any}, {"Ecuador_155", type any}, {"Ecuador_156", escriba any}, {"Ecuador_157", escriba any}, {"Ecuador_158", escriba any}, {"Ecuador_159", escriba any}, {"Ecuador_160", escriba any}, {"Ecuador_161", escriba any}, {"Ecuador_162", escriba any}, {"Ecuador_163", type any}, {"Ecuador_164", type any}, {"Ecuador_165", type any}, {"Ecuador_166", type any}, {"Ecuador_167", type any}, {"Ecuador_168", type any}, {"Ecuador_169", type any}, {"Ecuador_170", type any}, {"Ecuador_171", type any}, {"Ecuador_172", type any}, {"Ecuador_173", type any}, {"Ecuador_174", type any}, {"Ecuador_175", escriba any}, {"Ecuador_176", escriba any}, {"Ecuador_177", escriba any}, {"Ecuador_178", escriba any}, {"Ecuador_179", escriba any}, {"Ecuador_180", escriba any}, {"Ecuador_181", escriba any}, {"Ecuador_182", escriba any}, {"Ecuador_183", escriba any}, {"Ecuador_184", escriba any}, {"Ecuador_185", type any}, {"México", type any}, {"México_186", type any}, {"México_187", type any}, {"México_188", type any}, {"México_189", escriba any}, {"México_190", escriba any}, {"México_191", escriba any}, {"México_192", type any}, {"México_193", type any}, {"México_194", type any}, {"México_195", type any}, {"México_196", type any}, {"México_197", type any}, {"México_198", type any}, {"México_199", type any}, {"México_200", type any}, {"México_201", type any}, {"México_202", type any}, {"México_203", escriba any}, {"México_204", escriba any}, {"México_205", type any}, {"México_206", type any}, {"México_207", type any}, {"México_208", type any}, {"México_209", type any}, {"México_210", type any}, {"México_211", type any}, {"México_212", type any}, {"México_213", type any}, {"México_214", type any}, {"México_215", type any}, {"México_216", type any}, {"Panamá", escriba any}, {"Panamá_217", escriba any}, {"Panamá_218", escriba any}, {"Panamá_219", escriba any}, {"Panamá_220", escriba any}, {"Panamá_221", escriba any}, {"Panamá_222", type any}, {"Panamá_223", type any}, {"Panamá_224", type any}, {"Panamá_225", type any}, {"Panamá_226", type any}, {"Panamá_227", type any}, {"Panamá_228", type any}, {"Panamá_229", type any}, {"Panamá_230", escriba any}, {"Panamá_231", escriba any}, {"Panamá_232", escriba any}, {"Panamá_233", escriba any}, {"Panamá_234", type any}, {"Panamá_235", type any}, {"Panamá_236", type any}, {"Panamá_237", type any}, {"Panamá_238", type any}, {"Panamá_239", type any}, {"Panamá_240", type any}, {"Panamá_241", type any}, {"Panamá_242", type any}, {"Panamá_243", type any}, {"Panamá_244", escriba cualquiera}, {"Panamá_245", escriba cualquiera}, {"Panamá_246", escriba cualquiera}, {"Panamá_247", escriba cualquiera}, {"Paraguay", escriba cualquiera}, {"Paraguay_248", escriba cualquiera}, {"Paraguay_249", escriba cualquiera}, {"Paraguay_250", escriba cualquiera}, {"Paraguay_251", escriba cualquiera}, {"Paraguay_252", escriba cualquiera}, {"Paraguay_253", escriba cualquiera}, {"Paraguay_254", escriba cualquiera}, {"Paraguay_255 Paraguay_256", escriba cualquier}, {"Paraguay_257", escriba cualquier}, {"Paraguay_258", escriba any}, {"Paraguay_259", escriba any}, {"Paraguay_260", escriba any}, {"Paraguay_261", escriba any}, {"Perú", escriba any}, {"Perú_262", type any}, {"Perú_263", type any}, {"Perú_264", type any}, {"Perú_265", type any}, {"Perú_266", type any}, {"Perú_267", type any}, {"Perú_268", type any}, {"Perú_269", type any}, {"Perú_270", type any}, {"Perú_271", escriba any}, {"Perú_272", escriba any}, {"Perú_273", type any}, {"Perú_274", type any}, {"Perú_275", type any}, {"Perú_276", type any}, {"Perú_277", type any}, {"Perú_278", type any}, {"Perú_279", type any}, {"Perú_280", type any}, {"Perú_281", type any}, {"Perú_282", type any}, {"Perú_283", type any}, {"Perú_284", type any}, {"Perú_285", type any}, {"Perú_286", type any}, {"Perú_287", type any}, {"Perú_288", type any}, {"Perú_289", type any}, {"Perú_290", type any}, {"Perú_291", type any}, {"Perú_292", type any}, {"Trinidad y Tobago", type any}, {"Trinidad y Tobago_293", type any}, {"Trinidad y Tobago_294", type any}, {"Trinidad y Tobago_295", type any}, {"Trinidad y Tobago_296", escriba any}, {"Trinidad y Tobago_297", escriba any}, {"Trinidad y Tobago_298", escriba any}, {"Trinidad y Tobago_299", escriba any}, {"Trinidad y Tobago_300", escriba any}, {"Trinidad y Tobago_301", escriba any}, {"Trinidad y Tobago_302", escriba any}, {"Trinidad y Tobago_303", escriba any}, {"Trinidad y Tobago_304", escriba any}, {"Trinidad y Tobago_305", escriba any}, {"Trinidad y Tobago_306", escriba any}, {"Trinidad y Tobago_307", escriba any}, {"Trinidad y Tobago_308", escriba any}, {"Trinidad y Tobago_309", type any}, {"Trinidad y Tobago_310", type any}, {"Trinidad y Tobago_311", type any}, {"Trinidad y Tobago_312", type any}, {"Trinidad y Tobago_313", type any}, {"Trinidad y Tobago_314", type any}, {"Trinidad y Tobago_315", escriba any}, {"Trinidad y Tobago_316", escriba any}, {"Trinidad y Tobago_317", type any}, {"Trinidad y Tobago_318", type any}, {"Trinidad y Tobago_319", type any}, {"Trinidad y Tobago_320", type any}, {"Trinidad y Tobago_321", type any}, {"Trinidad y Tobago_322", type any}, {"Uruguay", type any}, {"Uruguay_323", type any}, {"Uruguay_324", type any}, {"Uruguay_325", escriba any}, {"Uruguay_326", escriba any}, {"Uruguay_327", escriba any}, {"Uruguay_328", escriba any}, {"Uruguay_329", escriba any}, {"Uruguay_330", escriba any}, {"Uruguay_331", escriba any}, {"Uruguay_332", escriba any}, {"Uruguay_333", escriba any}, {"Uruguay_334", escriba any}, {"Uruguay_335", escriba any}, {"Uruguay_336", escriba any}, {"Uruguay_337", escriba any}, {"Uruguay_338", escriba any}, {"Uruguay_339", escriba any}, {"Uruguay_340", type any}, {"Uruguay_341", type any}, {"Uruguay_342", type any}, {"Uruguay_343", type any}, {"Uruguay_344", type any}, {"Uruguay_345", type any}, {"Uruguay_346", type any}, {"Uruguay_347", type any}, {"Uruguay_348", type any}, {"Uruguay_349", escriba any}, {"Uruguay_350", escriba any}, {"Uruguay_351", escriba any}, {"Uruguay_352", escriba any}, {"Uruguay_353", escriba any}}),
#"Filas superiores eliminadas" = Table.Skip(#"Tipo cambiado",2),
#"Columnas renombradas" = Table.RenameColumns(#"Filas superiores eliminadas",{{"a", "Segmento"}, {"a_1", "Combustible"}}),
#"Otras columnas no votadas" = Table.UnpivotOtherColumns(#"Columnas renombradas", {"Segmento", "Combustible"}, "Atributo", "Valor"),
Source1 = Excel.Workbook(File.Contents("C:UsersPaulOneDrive1 Shared webComm PBIsPQ Tablevolumenes-regionales_Tipo de motor x pais y segmento.xlsx"), null, true),
Sheet0_Sheet1 = Source{[Item="Sheet0",Kind="Sheet"]}[Data],
#"Filas filtradas1" = Table.SelectRows(Sheet0_Sheet1, cada uno ([Columna1] <> null)),
#"Filas superiores eliminadas1" = Table.Skip(#"Filas filtradas1",1),
#"Encabezados promocionados1" = Table.PromoteHeaders(#"Filas superiores eliminadas1", [PromoteAllScalars=true]),
#"Filas superiores eliminadas2" = Table.Skip(#"Encabezados promocionados1",1),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Top Rows2",{{"a", "Segmento"}, {"a_1", "Combustible"}}),
#"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(#"Renamed Columns1", { "Segmento", "Combustible"}, "Attribute", "Value"),
Source2 = Table.NestedJoin(#"Unpivoted Other Columns1", {"Segmento", "Combustible", "Value"}, #"Unpivoted Other Columns", {"Segmento", "Combustible", "Value"}, "Sheet0", JoinKind.LeftOuter),
#"Hoja expandida1" = Table.ExpandTableColumn(Source2, "Sheet0", {"Attribute", "Value"}, {"Sheet0.Attribute", "Sheet0.Value"}),
#"Columnas eliminadas2" = Table.RemoveColumns(#"Hoja expandida1",{"Valor"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Sheet0.Value", "Mejor volumen disponible"}, {"Sheet0.Attribute", "País"}, {"Attribute", "Periodo"}}),
#"Texto extraído antes del delimitador" = Table.TransformColumns(#"Renamed Columns2", {{"País", cada Text.BeforeDelimiter(_, "_"), escriba text}}),
#"Texto extraído antes del delimitador1" = Table.TransformColumns(#"Texto extraído antes del delimitador", {{"Periodo", cada Text.BeforeDelimiter(_, "_"), escriba texto}}),
#"Filtered Rows2" = Table.SelectRows(#"Texto extraído antes del delimitador1", cada uno ([Mejor volumen disponible] <> "")),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows2",{{"Mejor volumen disponible", Int64.Type}, {"Segmento", type text}, {"Combustible", type text}})
en
#"Tipo cambiado1"
He adjuntado el archivo PBIX de ejemplo
