Forum Discussion
Unpivot attribute column names
Hello,
I have a problem when I unpivot multiple columns. When I unpivot, it puts all the columns in one column names called "Attribute" and it removes all the spaces of the columns. Is there a way that I can prevent this from happening or a function I could run to re-add the spaces back? I renamed the columns from "Attributes" to "Questions"
Here is the column I would like to get spaces back:
Thanks!
Hi Anonymous,
Please download the demo from the attachment. I would suggest you solve it by two steps instead one step in order to avoid long formula.
Text.Combine(List.ReplaceMatchingItems(Text.ToList([Attribute]), {{"A"," A"},{"B"," B"},{"C"," C"},{"D"," D"},{"E"," E"},{"F"," F"},{"G"," G"},{"H"," H"},{"I"," I"},{"J"," J"},{"K"," K"},{"L"," L"},{"M"," M"},{"N"," N"},{"O"," O"},{"P"," P"},{"Q"," Q"},{"R"," R"},{"S"," S"},{"T"," T"},{"U"," U"},{"V"," V"},{"W"," W"},{"X"," X"},{"Y"," Y"},{"Z"," Z"},{"0"," 0"},{"1"," 1"},{"2"," 2"},{"3"," 3"},{"4"," 4"},{"5"," 5"},{"6"," 6"},{"7"," 7"},{"8"," 8"},{"9"," 9"}}))Text.Trim(List.Accumulate(Text.Split([new], " "), "", (seed, current) => if List.Contains({"0","1","2","3","4","5","6","7","8","9"} , current) and List.Contains({"0","1","2","3","4","5","6","7","8","9"} , Text.End(seed, 1) ) then seed & current else if List.Contains({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"} , current) and List.Contains({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"} , Text.End(seed, 1)) then seed & current else seed & " " & current), " ")BTW, is this thread the same topic?
Best Regards,
Dale
1 Reply
Hi Anonymous,
Please download the demo from the attachment. I would suggest you solve it by two steps instead one step in order to avoid long formula.
Text.Combine(List.ReplaceMatchingItems(Text.ToList([Attribute]), {{"A"," A"},{"B"," B"},{"C"," C"},{"D"," D"},{"E"," E"},{"F"," F"},{"G"," G"},{"H"," H"},{"I"," I"},{"J"," J"},{"K"," K"},{"L"," L"},{"M"," M"},{"N"," N"},{"O"," O"},{"P"," P"},{"Q"," Q"},{"R"," R"},{"S"," S"},{"T"," T"},{"U"," U"},{"V"," V"},{"W"," W"},{"X"," X"},{"Y"," Y"},{"Z"," Z"},{"0"," 0"},{"1"," 1"},{"2"," 2"},{"3"," 3"},{"4"," 4"},{"5"," 5"},{"6"," 6"},{"7"," 7"},{"8"," 8"},{"9"," 9"}}))Text.Trim(List.Accumulate(Text.Split([new], " "), "", (seed, current) => if List.Contains({"0","1","2","3","4","5","6","7","8","9"} , current) and List.Contains({"0","1","2","3","4","5","6","7","8","9"} , Text.End(seed, 1) ) then seed & current else if List.Contains({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"} , current) and List.Contains({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"} , Text.End(seed, 1)) then seed & current else seed & " " & current), " ")BTW, is this thread the same topic?
Best Regards,
Dale