Unpivot some columns but retain others (variable data types)

[Anonymous]

hi all, 

I'm a power BI newbie and trying to get a handle on unpivoting multi-header columns. I followed the tips mentioned on the well detailed page (https://www.newtechdojo.com/unpivot-columns-in-power-bi/#dualrow) . However, it slightly more complicated than the one shown on the page. 

Link to the data set on G Drive--> https://drive.google.com/open?id=1WOI2NmWiPXGCeHs268VZLmKLiI0eA9Mx

To summarise the problem statement. My current dataset sits something like this (there are around 25 markets) 

				market01			market 02
channel	date	title	programme	datatype1	...	datatype6	datatype1	...	datatype6

the required output would need to look this:

channel	date	title	programe	market	datatype1	datatype2	datatype3	...	dataype6
				market01
				market01
				market02
				market02

the normal unpivot solutions all are pointing in the direction where the 6 dataypes on the right hand side get merged into a single column. only problem with that approach is they are all different datatypes (numeric, time etc) 

I believe custom coding in M holds some magic to unlock this mystery. I have very little knowledge of M and am knocking on this forum's doors. How does one go about doing this? 

Edit: I must also mention that I am dealing with an extremely large excel sheets here. These are massive data dumps around 600MB of excel files each. Hence I am wary of not grinding my machine to a halt with excessive & undesirable unpivot queries. I tried it doing unpivotting one bunch of column at a time but the number of rows increasing exponentially. This is what leads me to believe that M coding might do the trick. 

[dax · Accepted solution]

Hi @Anonymous , 

You could try to refer to below M code to see whether it work or not

let
    Source = Excel.Workbook(File.Contents("your excel path"), null, true),
    #"RAW data_Sheet" = Source{[Item="RAW data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"RAW data_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Target_group_01", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type any}, {"Column11", type any}, {"Column12", type text}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type any}, {"Column38", type any}, {"Column39", type any}, {"Column40", type any}, {"Column41", type any}, {"Column42", type any}, {"Column43", type any}, {"Column44", type any}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}, {"Column48", type any}, {"Column49", type any}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}, {"Column56", type any}, {"Column57", type any}, {"Column58", type any}, {"Column59", type any}, {"Column60", type any}, {"Column61", type any}, {"Column62", type any}, {"Column63", type any}, {"Column64", type any}, {"Column65", type any}, {"Column66", type any}, {"Column67", type any}, {"Column68", type any}, {"Column69", type any}, {"Column70", type any}, {"Column71", type any}, {"Column72", type any}, {"Column73", type any}, {"Column74", type any}, {"Column75", type any}, {"Column76", type any}, {"Column77", type any}, {"Column78", type any}, {"Column79", type any}, {"Column80", type any}, {"Column81", type any}, {"Column82", type any}, {"Column83", type any}, {"Column84", type any}, {"Column85", type any}, {"Column86", type any}, {"Column87", type any}, {"Column88", type any}, {"Column89", type any}, {"Column90", type any}, {"Column91", type any}, {"Column92", type any}, {"Column93", type any}, {"Column94", type any}, {"Column95", type any}, {"Column96", type any}, {"Column97", type any}, {"Column98", type any}, {"Column99", type any}, {"Column100", type any}, {"Column101", type any}, {"Column102", type any}, {"Column103", type any}, {"Column104", type any}, {"Column105", type any}, {"Column106", type any}, {"Column107", type any}, {"Column108", type any}, {"Column109", type any}, {"Column110", type any}, {"Column111", type any}, {"Column112", type any}, {"Column113", type any}, {"Column114", type any}, {"Column115", type any}, {"Column116", type any}, {"Column117", type any}, {"Column118", type any}, {"Column119", type any}, {"Column120", type any}, {"Column121", type any}, {"Column122", type any}, {"Column123", type any}, {"Column124", type any}, {"Column125", type any}, {"Column126", type any}, {"Column127", type any}, {"Column128", type any}, {"Column129", type any}, {"Column130", type any}, {"Column131", type any}, {"Column132", type any}, {"Column133", type any}, {"Column134", type any}, {"Column135", type any}, {"Column136", type any}, {"Column137", type any}, {"Column138", type any}, {"Column139", type any}, {"Column140", type any}, {"Column141", type any}, {"Column142", type any}, {"Column143", type any}, {"Column144", type any}, {"Column145", type any}, {"Column146", type any}, {"Column147", type any}, {"Column148", type any}, {"Column149", type any}, {"Column150", type any}, {"Column151", type any}, {"Column152", type any}, {"Column153", type any}, {"Column154", type any}, {"Column155", type any}, {"Column156", type any}, {"Column157", type any}, {"Column158", type any}, {"Column159", type any}, {"Column160", type any}, {"Column161", type any}, {"Column162", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Column2", "Column1", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26"}),
    #"Merged Columns" = Table.CombineColumns(#"Reordered Columns",{"Column2", "Column1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers1", {"Program theme ", "Dur (HH:MM:SS) ", "Dur (sec) ", "Programme Language ", "genre ", "Programme ", "end time ", "Start time ", "Week ", "Weekday ", "date ", "Channel "}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.1]), "Attribute.1", "Value")
in
    #"Pivoted Column"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[artemus]

You can combine the columns like:

Table.CombineColumns(myTable, {"data_type01", "data_type02", ..., "data_type06"}, each [data_type01 = _{0}, data_type02 = _{1}, ..., data_type06 = _{5}], "data_types")

This will compress the columns into a single column while retaining type information.