Re: Table Type Help

Syndicate_Admin · ‎09-29-2021

Good night.

I'm new to the forum and a little new to Power BI. I started last year but I never registered here until now that I see myself in a problem that I do not find any solution on the internet.

The situation is as follows, I received an excel like this:

To explain a little, it is a kind of table generated by a system in which vehicle sales information is shown by segment and the type of fuel. Above all is the country,below the period to which it corresponds (month-year).

I was asked to create visualizations so that you can distinguish the volume of sales according to segments, fuels, periods and countries (Let's say, for example, create a visualization in which the "Medium Sedans" can see the sales of the "electric" fuel type to see their evolution during the year 2019).

The problem is that I can't think of how to order this via Power Query so that all the filters that they request can be combined. I was trying to transpose it to order it by period (Then accompany it with a calendar table) but it looks very bad, like this:

Highlight in yellow the number of problems that are noticeable to the naked eye in my attempt to order it. The worst is the fact that I can't think of how to categorize it by fuel type since the columns are already occupied by segment.

That's it, hopefully my problem has been understood.

If you want any extra information, I will give it to you without drama.

I look forward to the help of the community! Thanks for reading!

PaulDBrown · ‎10-07-2021

Try with this (type the code into a blank query andpoint the source to your file obviously)

let
    Source = Excel.Workbook(File.Contents("C:\Users\Paul\OneDrive\1 Shared web\Comm PBIs\PQ Table\volumenes-regionales_Tipo de motor x pais y segmento.xlsx"), null, true),
    Sheet0_Sheet = Source{[Item="Sheet0",Kind="Sheet"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Sheet0_Sheet, each ([Column1] <> null)),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"a", type text}, {"a_1", type text}, {"Argentina", type any}, {"Argentina_2", type any}, {"Argentina_3", type any}, {"Argentina_4", type any}, {"Argentina_5", type any}, {"Argentina_6", type any}, {"Argentina_7", type any}, {"Argentina_8", type any}, {"Argentina_9", type any}, {"Argentina_10", type any}, {"Argentina_11", type any}, {"Argentina_12", type any}, {"Argentina_13", type any}, {"Argentina_14", type any}, {"Argentina_15", type 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", type any}, {"Bolivia_41", type 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", type any}, {"Chile_68", type any}, {"Chile_69", type any}, {"Chile_70", type any}, {"Chile_71", type 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", type any}, {"Chile_82", type any}, {"Chile_83", type any}, {"Chile_84", type any}, {"Chile_85", type 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", type any}, {"Colombia_95", type 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", type any}, {"Colombia_109", type any}, {"Colombia_110", type any}, {"Colombia_111", type any}, {"Colombia_112", type any}, {"Colombia_113", type any}, {"Colombia_114", type any}, {"Colombia_115", type any}, {"Colombia_116", type any}, {"Colombia_117", type any}, {"Colombia_118", type any}, {"Colombia_119", type any}, {"Colombia_120", type any}, {"Colombia_121", type any}, {"Colombia_122", type any}, {"Colombia_123", type any}, {"Costa Rica", type any}, {"Costa Rica_124", type any}, {"Costa Rica_125", type any}, {"Costa Rica_126", type any}, {"Costa Rica_127", type any}, {"Costa Rica_128", type any}, {"Costa Rica_129", type any}, {"Costa Rica_130", type any}, {"Costa Rica_131", type any}, {"Costa Rica_132", type any}, {"Costa Rica_133", type any}, {"Costa Rica_134", type any}, {"Costa Rica_135", type any}, {"Costa Rica_136", type any}, {"Costa Rica_137", type any}, {"Costa Rica_138", type any}, {"Costa Rica_139", type any}, {"Costa Rica_140", type any}, {"Costa Rica_141", type any}, {"Costa Rica_142", type any}, {"Costa Rica_143", type any}, {"Costa Rica_144", type any}, {"Costa Rica_145", type 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", type any}, {"Ecuador_157", type any}, {"Ecuador_158", type any}, {"Ecuador_159", type any}, {"Ecuador_160", type any}, {"Ecuador_161", type any}, {"Ecuador_162", type 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", type any}, {"Ecuador_176", type any}, {"Ecuador_177", type any}, {"Ecuador_178", type any}, {"Ecuador_179", type any}, {"Ecuador_180", type any}, {"Ecuador_181", type any}, {"Ecuador_182", type any}, {"Ecuador_183", type any}, {"Ecuador_184", type 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", type any}, {"México_190", type any}, {"México_191", type 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", type any}, {"México_204", type 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á", type any}, {"Panamá_217", type any}, {"Panamá_218", type any}, {"Panamá_219", type any}, {"Panamá_220", type any}, {"Panamá_221", type 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", type any}, {"Panamá_231", type any}, {"Panamá_232", type any}, {"Panamá_233", type 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", type any}, {"Panamá_245", type any}, {"Panamá_246", type any}, {"Panamá_247", type any}, {"Paraguay", type any}, {"Paraguay_248", type any}, {"Paraguay_249", type any}, {"Paraguay_250", type any}, {"Paraguay_251", type any}, {"Paraguay_252", type any}, {"Paraguay_253", type any}, {"Paraguay_254", type any}, {"Paraguay_255", type any}, {"Paraguay_256", type any}, {"Paraguay_257", type any}, {"Paraguay_258", type any}, {"Paraguay_259", type any}, {"Paraguay_260", type any}, {"Paraguay_261", type any}, {"Perú", type 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", type any}, {"Perú_272", type 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", type any}, {"Trinidad y Tobago_297", type any}, {"Trinidad y Tobago_298", type any}, {"Trinidad y Tobago_299", type any}, {"Trinidad y Tobago_300", type any}, {"Trinidad y Tobago_301", type any}, {"Trinidad y Tobago_302", type any}, {"Trinidad y Tobago_303", type any}, {"Trinidad y Tobago_304", type any}, {"Trinidad y Tobago_305", type any}, {"Trinidad y Tobago_306", type any}, {"Trinidad y Tobago_307", type any}, {"Trinidad y Tobago_308", type 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", type any}, {"Trinidad y Tobago_316", type 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", type any}, {"Uruguay_326", type any}, {"Uruguay_327", type any}, {"Uruguay_328", type any}, {"Uruguay_329", type any}, {"Uruguay_330", type any}, {"Uruguay_331", type any}, {"Uruguay_332", type any}, {"Uruguay_333", type any}, {"Uruguay_334", type any}, {"Uruguay_335", type any}, {"Uruguay_336", type any}, {"Uruguay_337", type any}, {"Uruguay_338", type any}, {"Uruguay_339", type 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", type any}, {"Uruguay_350", type any}, {"Uruguay_351", type any}, {"Uruguay_352", type any}, {"Uruguay_353", type any}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",2),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows",{{"a", "Segmento"}, {"a_1", "Combustible"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Segmento", "Combustible"}, "Attribute", "Value"),
    Source1 = Excel.Workbook(File.Contents("C:\Users\Paul\OneDrive\1 Shared web\Comm PBIs\PQ Table\volumenes-regionales_Tipo de motor x pais y segmento.xlsx"), null, true),
    Sheet0_Sheet1 = Source{[Item="Sheet0",Kind="Sheet"]}[Data],
    #"Filtered Rows1" = Table.SelectRows(Sheet0_Sheet1, each ([Column1] <> null)),
    #"Removed Top Rows1" = Table.Skip(#"Filtered Rows1",1),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows1", [PromoteAllScalars=true]),
    #"Removed Top Rows2" = Table.Skip(#"Promoted Headers1",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),
    #"Expanded Sheet1" = Table.ExpandTableColumn(Source2, "Sheet0", {"Attribute", "Value"}, {"Sheet0.Attribute", "Sheet0.Value"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded Sheet1",{"Value"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Sheet0.Value", "Mejor volumen disponible"}, {"Sheet0.Attribute", "País"}, {"Attribute", "Periodo"}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Renamed Columns2", {{"País", each Text.BeforeDelimiter(_, "_"), type text}}),
    #"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Extracted Text Before Delimiter", {{"Periodo", each Text.BeforeDelimiter(_, "_"), type text}}),
    #"Filtered Rows2" = Table.SelectRows(#"Extracted Text Before Delimiter1", each ([Mejor volumen disponible] <> "")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows2",{{"Mejor volumen disponible", Int64.Type}, {"Segmento", type text}, {"Combustible", type text}})
in
    #"Changed Type1"

I've attached the sample PBIX file

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.