Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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 NameCryogenic EquipmentDental Care EquipmentHome Healthcare EquipmentMedical EquipmentMedical Testing, Analyzing, and Diagnostic EquipmentTherapeutic DevicesVeterinary EquipmentMedical ConsumablesMedical InstrumentsOptical HealthcareLatitudeLongitude
.decimal, Inc.000001000028.80052-81.3188
21st Century Scientific, Inc.000001000047.72617-116.838
3D Medical Manufacturing, Inc.010001010026.77606-80.068
3M Cardiovascular Devices, Inc.000011000033.71933-117.834
3m Caribe Llc000000010039.49591-98.99
3M Co., Orthopedic Implants Business000001000044.95045-92.9968
3M Unitek Corporation010000000034.11586-118.003
3T Ophthalmics, Inc.000010000033.6873-117.826
6S Medical LLC000000010040.767-111.831
A Company Orthodontics010000000032.89887-117.115
A&D Medical000110000037.39304-121.886
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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"

1.png


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

2.png

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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"

1.png


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

2.png

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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...)

 

   
PeriodCustomerNameRevenueCost2-Labor3-Contract LaborProdHoursGPGM%
201806CUSTOMER 12,704,3332,045,0751,124,330225,39445518 CALCULATED MEASURE  CALCULATED MEASURE
201808CUSTOMER 12,331,3381,761,920941,523223,40137580  
201803CUSTOMER 12,423,9902,042,2061,032,529218,48540718  
201812CUSTOMER 2493,270452,819110,539208,8473544  
201812CUSTOMER 3159,040247,4563,390187,6750  
201812CUSTOMER 13,121,8642,262,5021,303,732181,80853764  
201807CUSTOMER 22,091,9971,569,714873,601173,91334731  
201809CUSTOMER 4856,106733,216308,693163,4293162  
201812CUSTOMER 21,106,040779,390352,234152,4670  
201802CUSTOMER 2601,923431,573117,819150,5773762  
201805CUSTOMER 12,080,9901,533,266876,498150,43835511  

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.