Unpivot columns

[Anonymous]

Hi there, 

I am trying to make a map based on the info below. The end goal is when I choose any product (Cryogenic Equipment, Dental Care Equipment, Home Healthcare Equipment, etc.) from a slicer besides the map, the relevant companies will appear. 

I was told to use unpivot table but still can't figure out how. Please give me some suggestions/ideas, much appreciate it!

Company Name	Cryogenic Equipment	Dental Care Equipment	Home Healthcare Equipment	Medical Equipment	Medical Testing, Analyzing, and Diagnostic Equipment	Therapeutic Devices	Veterinary Equipment	Medical Consumables	Medical Instruments	Optical Healthcare	Latitude	Longitude
.decimal, Inc.	0	0	0	0	0	1	0	0	0	0	28.80052	-81.3188
21st Century Scientific, Inc.	0	0	0	0	0	1	0	0	0	0	47.72617	-116.838
3D Medical Manufacturing, Inc.	0	1	0	0	0	1	0	1	0	0	26.77606	-80.068
3M Cardiovascular Devices, Inc.	0	0	0	0	1	1	0	0	0	0	33.71933	-117.834
3m Caribe Llc	0	0	0	0	0	0	0	1	0	0	39.49591	-98.99
3M Co., Orthopedic Implants Business	0	0	0	0	0	1	0	0	0	0	44.95045	-92.9968
3M Unitek Corporation	0	1	0	0	0	0	0	0	0	0	34.11586	-118.003
3T Ophthalmics, Inc.	0	0	0	0	1	0	0	0	0	0	33.6873	-117.826
6S Medical LLC	0	0	0	0	0	0	0	1	0	0	40.767	-111.831
A Company Orthodontics	0	1	0	0	0	0	0	0	0	0	32.89887	-117.115
A&D Medical	0	0	0	1	1	0	0	0	0	0	37.39304	-121.886

[Anonymous · Accepted solution]

Hi @Anonymous,

You can refer to following steps to create a map visual and slicer to achieve your retirement.

1. Enter to query editor and do 'unpivot columns' on all product columns, then save changes to dateview.

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZLLbsIwEEV/xco6jPxI/Fi2sEECsaBdURauE4rVxInyQOLv6yQURBtoWUSyZnHu3JPZbAJIUmNznYVo7gwEYYBHPjIyoxIkxjH1z4kkwIiUwTbcBJTUDZqmrmmrI1ob6192Z83D/EiAoJyIjk8IB8kGPpuhZZpYozO01K7daeOTrPu4CvgJJCNzykEIjnlfAAPmJ/4STXWV2OKga9NmukKz9GBNWt9sQG40YAwEUYwNDYRvEA0JeZdg31O0yMwNI2NmmIJIxaobTZQEpc77FhCiVdXsi7Izg+Z5mWnX1Oi5ra1L6/oR7RGoGEdxH0J9yEXLq7NN+unTqrKodGMLd4fzS0YEhMSSDzIkYMwG7gtalftmr7PcmvuOR7EMuBQXxZT3VL4+H8liMX3AcYRB8NPNEf/HSI978qXzUrvjYDkp/E2b+o/VrtakIJWU4ntPr2IAv7UYU34+6X9eVo8UwBTDUY+kflfvdrv9Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Company Name" = _t, #"Cryogenic Equipment" = _t, #"Dental Care Equipment" = _t, #"Home Healthcare Equipment" = _t, #"Medical Equipment" = _t, #"Medical Testing, Analyzing, and Diagnostic Equipment" = _t, #"Therapeutic Devices" = _t, #"Veterinary Equipment" = _t, #"Medical Consumables" = _t, #"Medical Instruments" = _t, #"Optical Healthcare" = _t, Latitude = _t, Longitude = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company Name", type text}, {"Cryogenic Equipment", Int64.Type}, {"Dental Care Equipment", Int64.Type}, {"Home Healthcare Equipment", Int64.Type}, {"Medical Equipment", Int64.Type}, {"Medical Testing, Analyzing, and Diagnostic Equipment", Int64.Type}, {"Therapeutic Devices", Int64.Type}, {"Veterinary Equipment", Int64.Type}, {"Medical Consumables", Int64.Type}, {"Medical Instruments", Int64.Type}, {"Optical Healthcare", Int64.Type}, {"Latitude", type number}, {"Longitude", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company Name", "Latitude", "Longitude"}, "Product", "Value")
in
    #"Unpivoted Columns"

2. Create map visual and drag columns to correspond fields, then create a slicer based on unpivoted product column.

Regards,
Xiaoxin Sheng

[ssutton]

I need to create similar experience, except with calculated measures, hence they do not show up in query editor. 

Data looks similar to the previous example, with the exception of my two calculated measures.  The columns that need to be summarized or "unpivoted" are in bold below: (Revenue, Cost, 2-Labor, 3-Contract Labor...)

Period	CustomerName	Revenue	Cost	2-Labor	3-Contract Labor	ProdHours	GP	GM%
201806	CUSTOMER 1	2,704,333	2,045,075	1,124,330	225,394	45518	CALCULATED MEASURE	CALCULATED MEASURE
201808	CUSTOMER 1	2,331,338	1,761,920	941,523	223,401	37580
201803	CUSTOMER 1	2,423,990	2,042,206	1,032,529	218,485	40718
201812	CUSTOMER 2	493,270	452,819	110,539	208,847	3544
201812	CUSTOMER 3	159,040	247,456	3,390	187,675	0
201812	CUSTOMER 1	3,121,864	2,262,502	1,303,732	181,808	53764
201807	CUSTOMER 2	2,091,997	1,569,714	873,601	173,913	34731
201809	CUSTOMER 4	856,106	733,216	308,693	163,429	3162
201812	CUSTOMER 2	1,106,040	779,390	352,234	152,467	0
201802	CUSTOMER 2	601,923	431,573	117,819	150,577	3762
201805	CUSTOMER 1	2,080,990	1,533,266	876,498	150,438	35511