cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Table type help

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:

tomaspore_0-1632967301154.png

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:

tomaspore_1-1632967910277.png

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!

4 REPLIES 4
PaulDBrown
Super User
Super User

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"

 

result.JPG

 

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.






mussaenda
Resident Rockstar
Resident Rockstar

Hi @Syndicate_Admin ,

 

Have you tried to unpivot your data?

Can we have a sample data where we can copy and transform?

Sorry for the delay in answering.

Yes, try to do despivot but it still looks bad.

I provide you with the dataset if you want to help me:

https://1drv.ms/x/s!AtcD7ZppdbfugmvcmitP__-D2Uw0?e=oUfyQG

If you need some more information, just tell me. Best regards.

Hi  @Syndicate_Admin ,

 

I have checked your oringinal data,what is the expected output you need?

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.