Forum Discussion
Receiving Error After Unpivoting Columns in Power Query
I'm trying to alphabetize a column of email addresses, separated by a semi colon. I found if I added an index column, split by delimiter, unpivoted the columns, sorted the rows, grouped the rows, and...
Try below with better error handling:
let
Source = Excel.CurrentWorkbook(){[Name="ACR"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Type] = "Assets" or [Type] = "Links")),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows"," ","",Replacer.ReplaceText,{"ContentOwner"}),
#"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 1, 1, Int64.Type),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Index", "ContentOwner", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"ContentOwner.1", "ContentOwner.2", "ContentOwner.3", "ContentOwner.4", "ContentOwner.5"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"SiteCollection", type text}, {"Site", type text}, {"ContentId", Int64.Type}, {"ContentOwner.1", type text}, {"ContentOwner.2", type text}, {"ContentOwner.3", type text}, {"ContentOwner.4", type text}, {"ContentOwner.5", type text}, {"Title", type text}, {"Index", Int64.Type}}),
#"Replaced Value1" = Table.ReplaceValue(#"Changed Type",null,"Null",Replacer.ReplaceValue,{"ContentOwner.2", "ContentOwner.3", "ContentOwner.4", "ContentOwner.5"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value1", {"SiteCollection", "Site", "ContentId", "Title", "Index"}, "Attribute", "Value"),
#"Removed Errors" = Table.SelectRows(#"Unpivoted Columns", each not Text.Contains([Value], "#NAME?")),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Removed Errors",{{"Index", Order.Descending}, {"Value", Order.Ascending}}))
in
#"Sorted Rows"
I still got an error with using your code but when I clicked "Remove Errors" on the Index, Attribute, and Value columns, the error got cleared. Is there a way to see where the error was? I'm now unsure how accurate my information is if I can't see what was removed.
renee_crozier , I'd start from Promote Headers step. At left bottom of Power Query Editor window set "Column profiling based on entire data set". On ribbon -> View enable Column Quality.
Check for which columns number of errors is not 0%. Select these columns, on ribbon Home->Keep Rows->Keep errors. Most probably you may identify the source of errors here.
