Pivot Two Columns in Power Query (Without Error)

Опубликовано: 18 Октябрь 2024
на канале: Josh_Excel

186

#powerquery #power_query
Steps:
1. Perform Pivot Operation
2. Add each _ to replace error with a list of values in each column
Expression.Error: There weren't enough elements in the enumeration to complete the operation.
3. Add new Column to create a table with Table.FromColumns() function
4. Expand Table and add Header Names from Pivoted Column steps

Advanced Editor:

let
Source = null,
T1 = #table({"Attribute", "Value"},{{"Name", "John"}, {"Age", "25"}, {"Birthdate", "2/14/1998"}, {"City", "New York"}, {"Phone Number", "(123) 456-7890"}, {"Name", "Mary"}, {"Age", "32"}, {"Birthdate", "7/20/1991"}, {"City", "Chicago"}, {"Phone Number", "(234) 567-8901"}, {"Name", "David"}, {"Age", "45"}, {"Birthdate", "12/5/1978"}, {"City", "Los Angeles"}, {"Phone Number", "(345) 678-9012"}, {"Name", "Sarah"}, {"Age", "29"}, {"Birthdate", "5/22/1994"}, {"City", "Houston"}, {"Phone Number", "(456) 789-0123"}, {"Name", "Michael"}, {"Age", "38"}, {"Birthdate", "9/10/1983"}, {"City", "Miami"}, {"Phone Number", "(567) 890-1234"}, {"Name", "Emily"}, {"Age", "22"}, {"Birthdate", "4/8/2001"}, {"City", "Seattle"}, {"Phone Number", "(678) 901-2345"}, {"Name", "James"}, {"Age", "41"}, {"Birthdate", "1/30/1980"}, {"City", "Boston"}, {"Phone Number", "(789) 012-3456"}, {"Name", "Ashley"}, {"Age", "27"}, {"Birthdate", "8/17/1996"}, {"City", "Atlanta"}, {"Phone Number", "(890) 123-4567"}, {"Name", "William"}, {"Age", "33"}, {"Birthdate", "3/12/1990"}, {"City", "Dallas"}, {"Phone Number", "(901) 234-5678"}, {"Name", "Sophia"}, {"Age", "24"}, {"Birthdate", "6/23/1999"}, {"City", "Denver"}, {"Phone Number", "(012) 345-6789"}, {"Name", "Robert"}, {"Age", "31"}, {"Birthdate", "11/18/1992"}, {"City", "San Diego"}, {"Phone Number", "(123) 456-7890"}, {"Name", "Olivia"}, {"Age", "28"}, {"Birthdate", "2/3/1995"}, {"City", "Phoenix"}, {"Phone Number", "(234) 567-8901"}, {"Name", "Benjamin"}, {"Age", "36"}, {"Birthdate", "12/29/1987"}, {"City", "Portland"}, {"Phone Number", "(345) 678-9012"}, {"Name", "Ava"}, {"Age", "23"}, {"Birthdate", "5/15/2000"}, {"City", "Nashville"}, {"Phone Number", "(456) 789-0123"}, {"Name", "Christopher"}, {"Age", "30"}, {"Birthdate", "10/26/1993"}, {"City", "San Francisco"}, {"Phone Number", "(567) 890-1234"}, {"Name", "Elizabeth"}, {"Age", "26"}, {"Birthdate", "1/13/1997"}, {"City", "Austin"}, {"Phone Number", "(678) 901-2345"}, {"Name", "Joseph"}, {"Age", "39"}, {"Birthdate", "4/27/1984"}, {"City", "Philadelphia"}, {"Phone Number", "(789) 012-3456"}, {"Name", "Isabella"}, {"Age", "21"}, {"Birthdate", "7/11/2002"}, {"City", "Las Vegas"}, {"Phone Number", "(890) 123-4567"}, {"Name", "Daniel"}, {"Age", "35"}, {"Birthdate", "9/6/1988"}, {"City", "Washington D.C."}, {"Phone Number", "(901) 234-5678"}, {"Name", "Mia"}, {"Age", "27"}, {"Birthdate", "8/17/1996"}, {"City", "Miami Beach"}, {"Phone Number", "(012) 345-6789"}}),
#"Pivoted Column" = Table.Pivot(T1, List.Distinct(T1[Attribute]), "Attribute", "Value", each _),
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Data", each Table.FromColumns({[Name],[Age],[Birthdate],[City],[Phone Number]})),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5"}, Table.ColumnNames(#"Pivoted Column"))
in
#"Expanded Data"