Unpivot some columns but retain others (variable data types)

[crackednut]

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 (Unpivot Columns in Power BI - New Tech Dojo) . However, it slightly more complicated than the one shown on the page.



Link to the data set on G Drive--> unpivot test data_v02 sample.xlsx



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?

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.

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Unpivot some columns but retain others (variable data types)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[sandy666]

is that what you want?

part of data

#Channel	#date	#Weekday	#Week	#Start time	#end time	#Programme	#genre	#Programme Language	#Dur (sec)	#Dur (HH:MM:SS)	#Program theme	Market	data_type01	data_type02	data_type03	data_type04	data_type05	data_type06
channel02	43571	Tuesday	16	0.499907407	0.633078704	program18	genre01	lang01	11506	0.133171296	theme01	market01	651529.31	6507.96	1	28078.46	4.31	0.029490741
channel01	43646	Sunday	27	0.82337963	0.936597222	program16	genre01	lang01	9782	0.113217593	theme01	market01	649847.54	6502	1	32609.28	5.02	0.022256944
channel02	43563	Monday	15	0.833356481	0.962511574	program02	genre01	lang01	11159	0.129155093	theme01	market01	650009.58	13330.64	2.05	41297.75	6.35	0.040532407
channel01	43669	Tuesday	30	0.520717593	0.627083333	program07	genre01	lang01	9190	0.106365741	theme01	market01	651176.44	9894.7	1.52	32705.14	5.02	0.031053241
channel02	43710	Monday	36	0.500081019	0.622337963	program04	genre01	lang01	10563	0.122256944	theme01	market01	651972.82	12634.39	1.94	34135.02	5.24	0.043506944
channel02	43643	Thursday	26	0.833657407	0.968229167	program11	genre01	lang01	11627	0.134571759	theme01	market01	648693.6	7277.97	1.12	30723.75	4.74	0.03119213
channel02	43613	Tuesday	22	0.833333333	0.955706019	program12	genre01	lang01	10573	0.122372685	theme01	market01	650527.07	7276.67	1.12	34771.08	5.35	0.025150463
channel02	43518	Friday	8	0.500439815	0.636099537	program08	genre01	lang01	11721	0.135659722	theme01	market01	650948.62	8790.3	1.35	30794.38	4.73	0.037303241
channel01	43806	Saturday	49	0.833113426	0.951712963	program16	genre01	lang01	10247	0.118599537	theme01	market01	651774.57	6423.45	0.99	32888.09	5.05	0.022546296
channel01	43769	Thursday	44	0.82994213	0.956377315	program06	genre01	lang01	10924	0.126435185	theme01	market01	650147.53	9888.65	1.52	34626.56	5.33	0.034953704
channel01	43691	Wednesday	33	0.541724537	0.671990741	program13	genre01	lang01	11255	0.130266204	theme01	market01	650345.39	7049.11	1.08	28671.91	4.41	0.031331019
channel01	43824	Wednesday	52	0.520706019	0.613171296	program09	genre01	lang01	7989	0.092465278	theme01	market01	651786.19	8732.15	1.34	29422.94	4.51	0.026539352
channel02	43479	Monday	3	0.499884259	0.621273148	program14	genre01	lang01	10488	0.121388889	theme01	market01	651200.44	7032.69	1.08	27865.1	4.28	0.029363426
channel01	43849	Sunday	4	0.520914352	0.638993056	program03	genre01	lang01	10202	0.118078704	theme01	market01	651376.5	12940.14	1.99	37675.76	5.78	0.039386574
channel01	43846	Thursday	3	0.500069444	0.627430556	program01	genre01	lang01	11004	0.127361111	theme01	market01	649301.51	16436.92	2.53	42452.54	6.54	0.04744213
channel02	43776	Thursday	45	0.500173611	0.630300926	program05	genre01	lang01	11243	0.130127315	theme01	market01	650543.48	12448.49	1.91	42268.8	6.5	0.037071759
channel03	43619	Monday	23	0.755740741	0.837962963	program17	genre01	lang02	7104	0.082222222	theme01	market01	649477	6548.98	1.01	27490.53	4.23	0.019513889
channel04	43727	Thursday	38	0.830381944	0.987210648	program19	genre01	lang01	13550	0.156828704	theme01	market01	649888.1	6449.3	0.99	27303.04	4.2	0.035150463
channel03	43783	Thursday	46	0.790208333	0.883252315	program10	genre01	lang02	8039	0.093043981	theme01	market01	649490.24	7791.08	1.2	32520.23	5.01	0.022141204
channel04	43537	Wednesday	11	0.830162037	0.988217593	program20	genre01	lang01	13656	0.158055556	theme01	market01	650224.36	6407.49	0.99	40346.35	6.2	0.024444444
channel03	43733	Wednesday	39	0.775127315	0.876585648	program21	genre01	lang02	8766	0.101458333	theme01	market01	649801.52	6394.64	0.98	31761.04	4.89	0.020335648
channel03	43790	Thursday	47	0.750856481	0.8446875	program22	genre01	lang02	8107	0.093831019	theme01	market01	649406.54	6167.29	0.95	29945.99	4.61	0.019212963
channel03	43521	Monday	9	0.795833333	0.893368056	program15	genre01	lang02	8427	0.097534722	theme01	market01	650579.4	6906.74	1.06	27911.8	4.29	0.023888889
channel01	43518	Friday	8	0.463310185	0.579016204	program16	genre01	lang01	9997	0.115706019	theme01	market01	650948.62	6634.65	1.02	28919.09	4.44	0.025243056
channel01	43824	Wednesday	52	0.520706019	0.613171296	program09	genre01	lang01	7989	0.092465278	theme01	market02	445383.18	8561.44	1.92	28728.06	6.45	0.026631944
channel01	43846	Thursday	3	0.500069444	0.627430556	program01	genre01	lang01	11004	0.127361111	theme01	market02	443311.98	15866.09	3.58	41115.88	9.27	0.047303241
channel03	43790	Thursday	47	0.750856481	0.8446875	program22	genre01	lang02	8107	0.093831019	theme01	market02	443263.41	6067.17	1.37	29346.76	6.62	0.01931713
channel02	43571	Tuesday	16	0.499907407	0.633078704	program18	genre01	lang01	11506	0.133171296	theme01	market02	444857.85	6392.99	1.44	27395.84	6.16	0.029675926
channel01	43691	Wednesday	33	0.541724537	0.671990741	program13	genre01	lang01	11255	0.130266204	theme01	market02	443854.82	6811.12	1.53	27740.26	6.25	0.031273148
channel01	43669	Tuesday	30	0.520717593	0.627083333	program07	genre01	lang01	9190	0.106365741	theme01	market02	444498	9573.19	2.15	31786.73	7.15	0.030925926
channel01	43769	Thursday	44	0.82994213	0.956377315	program06	genre01	lang01	10924	0.126435185	theme01	market02	443777.9	9629.86	2.17	33584.56	7.57	0.035104167
channel01	43806	Saturday	49	0.833113426	0.951712963	program16	genre01	lang01	10247	0.118599537	theme01	market02	445096.16	6196.58	1.39	31742.68	7.13	0.022534722
channel04	43537	Wednesday	11	0.830162037	0.988217593	program20	genre01	lang01	13656	0.158055556	theme01	market02	443618.08	6126.8	1.38	37987.45	8.56	0.024803241
channel02	43613	Tuesday	22	0.833333333	0.955706019	program12	genre01	lang01	10573	0.122372685	theme01	market02	443942.13	7040.26	1.59	33686.52	7.59	0.025104167
channel03	43521	Monday	9	0.795833333	0.893368056	program15	genre01	lang02	8427	0.097534722	theme01	market02	444235.07	6766.49	1.52	27403.88	6.17	0.023854167
channel03	43619	Monday	23	0.755740741	0.837962963	program17	genre01	lang02	7104	0.082222222	theme01	market02	443327.3	6427.17	1.45	26928.62	6.07	0.019548611
channel02	43710	Monday	36	0.500081019	0.622337963	program04	genre01	lang01	10563	0.122256944	theme01	market02	444755.15	12188.18	2.74	32955.75	7.41	0.0434375
channel02	43776	Thursday	45	0.500173611	0.630300926	program05	genre01	lang01	11243	0.130127315	theme01	market02	444475.61	12164.73	2.74	41114.84	9.25	0.037222222
channel02	43643	Thursday	26	0.833657407	0.968229167	program11	genre01	lang01	11627	0.134571759	theme01	market02	442956.15	7039.14	1.59	29635.82	6.69	0.031284722
channel04	43727	Thursday	38	0.830381944	0.987210648	program19	genre01	lang01	13550	0.156828704	theme01	market02	443114.02	5979.06	1.35	25803.25	5.82	0.034409722
channel02	43479	Monday	3	0.499884259	0.621273148	program14	genre01	lang01	10488	0.121388889	theme01	market02	444050.82	6697.92	1.51	26786.07	6.03	0.0290625
channel03	43783	Thursday	46	0.790208333	0.883252315	program10	genre01	lang02	8039	0.093043981	theme01	market02	443435.4	7593.95	1.71	31718.25	7.15	0.02212963
channel01	43849	Sunday	4	0.520914352	0.638993056	program03	genre01	lang01	10202	0.118078704	theme01	market02	444495.05	12598.96	2.83	36761.6	8.27	0.039282407
channel02	43563	Monday	15	0.833356481	0.962511574	program02	genre01	lang01	11159	0.129155093	theme01	market02	443644.91	9827.66	2.22	33983.64	7.66	0.03630787
channel03	43733	Wednesday	39	0.775127315	0.876585648	program21	genre01	lang02	8766	0.101458333	theme01	market02	443579.19	6305.2	1.42	31115.98	7.01	0.020462963
channel02	43518	Friday	8	0.500439815	0.636099537	program08	genre01	lang01	11721	0.135659722	theme01	market02	444116.49	8597.01	1.94	29854.74	6.72	0.037627315
channel01	43518	Friday	8	0.463310185	0.579016204	program16	genre01	lang01	9997	0.115706019	theme01	market02	444116.49	6385.93	1.44	28085.28	6.32	0.025023148
channel01	43646	Sunday	27	0.82337963	0.936597222	program16	genre01	lang01	9782	0.113217593	theme01	market02	443214.65	6300.2	1.42	31702.44	7.15	0.0221875
channel03	43790	Thursday	47	0.750856481	0.8446875	program22	genre01	lang02	8107	0.093831019	theme01	market03	70372.93	230.98	0.33	1112.17	1.58	0.019490741
channel01	43849	Sunday	4	0.520914352	0.638993056	program03	genre01	lang01	10202	0.118078704	theme01	market03	70505.22	2368.18	3.36	6888.97	9.77	0.040162037

 

[crackednut]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Unpivot some columns but retain others (variable data types)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

apologies since I didn't not read the forums rules in detail before posting.

 

[crackednut]

is that what you want?

yes. this is exactly what i was looking for! so how exactly does one achieve this?

 

[sandy666]

here is M-code (Power Query) for this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TRC = Table.RemoveColumns(Table.FillDown(Table.AddColumn(Table.Transpose(Table.DemoteHeaders(Source)), "Custom", each if Text.Contains([Column1], "Column") then null else [Column1]),{"Custom"}),{"Column1"}),
    Reorder = Table.ReorderColumns(TRC,{"Custom", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26"}),
    Promote = Table.PromoteHeaders(Table.Transpose(Table.CombineColumns(Reorder,{"Custom", "Column2"},Combiner.CombineTextByDelimiter("#", QuoteStyle.None),"Merged")), [PromoteAllScalars=true]),
    UOC = Table.UnpivotOtherColumns(Promote, {"#Channel", "#date", "#Weekday", "#Week", "#Start time", "#end time", "#Programme", "#genre", "#Programme Language", "#Dur (sec)", "#Dur (HH:MM:SS)", "#Program theme"}, "Attribute", "Value"),
    Split = Table.SplitColumn(UOC, "Attribute", Splitter.SplitTextByAnyDelimiter({"#"}, QuoteStyle.Csv)),
    Promote1 = Table.PromoteHeaders(Table.Transpose(Table.ReplaceValue(Table.Transpose(Table.DemoteHeaders(Table.RenameColumns(Table.Sort(Table.Pivot(Split, List.Distinct(Split[Attribute.2]), "Attribute.2", "Value"),{{"Attribute.1", Order.Ascending}}),{{"Attribute.1", "Market"}}))),"#","",Replacer.ReplaceText,{"Column1"})), [PromoteAllScalars=true]),
    Type = Table.TransformColumnTypes(Promote1,{{"Channel", type text}, {"date", type date}, {"Weekday", type text}, {"Week", Int64.Type}, {"Start time", type time}, {"end time", type time}, {"Programme", type text}, {"genre", type text}, {"Programme Language", type text}, {"Dur (sec)", Int64.Type}, {"Dur (HH:MM:SS)", type time}, {"Program theme", type text}, {"Market", type text}, {"data_type01", type number}, {"data_type02", type number}, {"data_type03", type number}, {"data_type04", type number}, {"data_type05", type number}, {"data_type06", type number}})
in
    Type

s666-PQ-unpivot test data_v02 sample.xlsx

Microsoft Excel Workbook

1drv.ms

 

[crackednut]

thank you for the prompt response. will definitely check this out!

 

[sandy666]

for the future try to keep your source data in first normal form (1NF) , will be much easier
have a nice day