Unpivot some columns but retain others (variable data types)

Question

hi all,&nbsp;&nbsp;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&nbsp;page (https://www.newtechdojo.com/unpivot-columns-in-power-bi/#dualrow) . However, it slightly more complicated than the one shown on the page.&nbsp;&nbsp;Link to the data set on G Drive--&gt;&nbsp;https://drive.google.com/open?id=1WOI2NmWiPXGCeHs268VZLmKLiI0eA9Mx&nbsp;To summarise the problem statement. My current dataset sits something like this (there are around 25 markets)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;market01&nbsp;&nbsp;market 02&nbsp;&nbsp;channeldatetitleprogrammedatatype1...datatype6&nbsp;datatype1...datatype6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;the required output would need to look this:&nbsp;channeldatetitleprogramemarketdatatype1datatype2datatype3...dataype6&nbsp;&nbsp;&nbsp;&nbsp;market01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;market01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;market02&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;market02&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;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)&nbsp;&nbsp;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?&nbsp;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 &amp; 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.&nbsp;

dax · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,&nbsp;
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.
&nbsp;

Forum Discussion

Unpivot some columns but retain others (variable data types)

2 Replies

Related content

Unpivot Column

Unpivot columns

Pivot / Unpivot?

Unpivot Question

Unpivot

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