Forum Discussion
Unpivot columns
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 |
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
2 Replies
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 ShengI 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
