Need Help My Pivot Columns Turn Error

[Chanleakna123]

Hi Everyone, 

i'm on stage where i pivot my columns and those turn errors on Web URL , not i'm not really how to avoid such things. 

below is my M Code , i did all the way here , and those a lots of steps , 

let
Source = Csv.Document(Web.Contents("https://docs.google.com/spreadsheets/d/e/2PACX-1vRj6ZqgMhicXFiPh2Q_kbSN0o5m7Xdo0u1al1YidYVuYVWmVQr7E...",", Columns=86, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Promoted Headers", {{"Submission Date", type text}}, "en-US"), "Submission Date", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Submission Date.1", "Submission Date.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Submission Date.1", type date}, {"Submission Date.2", type time}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Submission Date.2"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Submission Date.1", "ថ្ងៃ ខែ ឆ្នាំ", "សេវនករ :", "ទីតាំងត្រួតពិនិត្យ:"}, "Attribute", "Value"),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Filled Down" = Table.FillDown(#"Changed Type2",{"Attribute.2"}),
#"Replaced Value" = Table.ReplaceValue(#"Filled Down","1","Q",Replacer.ReplaceText,{"Attribute.1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","No Label 2","Label",Replacer.ReplaceText,{"Attribute.1"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","No Label","Label",Replacer.ReplaceText,{"Attribute.1"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Label 3","Label",Replacer.ReplaceText,{"Attribute.1"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","0Q-Rate","Rate",Replacer.ReplaceText,{"Attribute.1"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","0Q-Score","Score",Replacer.ReplaceText,{"Attribute.1"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","អធិប្បាយ :","Comment",Replacer.ReplaceText,{"Attribute.1"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","2","Q",Replacer.ReplaceText,{"Attribute.1"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","Comment Q","Comment ",Replacer.ReplaceText,{"Attribute.1"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","Label 4","Label",Replacer.ReplaceText,{"Attribute.1"}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Replaced Value9", "Attribute.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute.1.1", "Attribute.1.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Attribute.1.1", type text}, {"Attribute.1.2", type text}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type3",{"Attribute.1.2"}),
#"Replaced Value10" = Table.ReplaceValue(#"Removed Columns2","03-Rate","Rate",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","0Q-Rate","Rate",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11","0Q-Score","Score",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12","03-Score","Score",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13","04-Rate","Rate",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value15" = Table.ReplaceValue(#"Replaced Value14","06-Rate","Rate",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value16" = Table.ReplaceValue(#"Replaced Value15","06-Score","Score",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value17" = Table.ReplaceValue(#"Replaced Value16","08-Score","Score",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value18" = Table.ReplaceValue(#"Replaced Value17","Label7","Label",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value19" = Table.ReplaceValue(#"Replaced Value18","04-Score","Score",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value20" = Table.ReplaceValue(#"Replaced Value19","3","Q",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value21" = Table.ReplaceValue(#"Replaced Value20","05-Rate","Rate",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value22" = Table.ReplaceValue(#"Replaced Value21","05-Score","Score",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value23" = Table.ReplaceValue(#"Replaced Value22","LabelQ","Label",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value24" = Table.ReplaceValue(#"Replaced Value23","8","Q",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value25" = Table.ReplaceValue(#"Replaced Value24","4","Q",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value26" = Table.ReplaceValue(#"Replaced Value25","Label0","Label",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value27" = Table.ReplaceValue(#"Replaced Value26","6","Q",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value28" = Table.ReplaceValue(#"Replaced Value27","9","Q",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value29" = Table.ReplaceValue(#"Replaced Value28","Label5","Label",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value30" = Table.ReplaceValue(#"Replaced Value29","QQ-Rate","Rate",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value31" = Table.ReplaceValue(#"Replaced Value30","LabelQ","Label",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value32" = Table.ReplaceValue(#"Replaced Value31","QQ-Score","Score",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value33" = Table.ReplaceValue(#"Replaced Value32","Q0-Rate","Rate",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value34" = Table.ReplaceValue(#"Replaced Value33","Q0","Q",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value35" = Table.ReplaceValue(#"Replaced Value34","Q-Score","Score",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value36" = Table.ReplaceValue(#"Replaced Value35","7","Q",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value37" = Table.ReplaceValue(#"Replaced Value36","5","Q",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value38" = Table.ReplaceValue(#"Replaced Value37","0Q-Rate","Rate",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value39" = Table.ReplaceValue(#"Replaced Value38","0Q-Score","Score",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Replaced Value40" = Table.ReplaceValue(#"Replaced Value39","0Score","Score",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Trimmed Text" = Table.TransformColumns(#"Replaced Value40",{{"Attribute.1.1", Text.Trim, type text}}),
#"Replaced Value41" = Table.ReplaceValue(#"Trimmed Text","QQ","Q",Replacer.ReplaceText,{"Attribute.1.1"}),
#"Reordered Columns" = Table.ReorderColumns(#"Replaced Value41",{"Submission Date.1", "ថ្ងៃ ខែ ឆ្នាំ", "សេវនករ :", "ទីតាំងត្រួតពិនិត្យ:", "Attribute.2", "Attribute.1.1", "Value"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Reordered Columns",{{"Value", type text}}),
#"Filtered Rows3" = Table.SelectRows(#"Changed Type4", each ([Attribute.1.1] <> "#Survey")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows3",{{"Value", Order.Ascending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Attribute.1.1] <> "IP" and [Attribute.1.1] <> "Submission" and [Attribute.1.1] <> "Total Percentage" and [Attribute.1.1] <> "Total Score") and ([Value] <> "") and ([#"សេវនករ :"] = "Vanny kean")),
#"Trimmed Text1" = Table.TransformColumns(#"Filtered Rows",{{"Attribute.2", Text.Trim, type text}}),
#"Split Column by Delimiter3" = Table.ExpandListColumn(Table.TransformColumns(#"Trimmed Text1", {{"Value", Splitter.SplitTextByDelimiter("] ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"ថ្ងៃ ខែ ឆ្នាំ", type text}, {"សេវនករ :", type text}, {"ទីតាំងត្រួតពិនិត្យ:", type text}, {"Value", type text}}),
#"Trimmed Text2" = Table.TransformColumns(#"Changed Type",{{"Value", Text.Trim, type text}}),
#"Filtered Rows1" = Table.SelectRows(#"Trimmed Text2", each ([Value] <> "[02A7C60A-1992-448C-9EA4-8A39C6D9B53B.jpeg" and [Value] <> "[053102CF-ACD3-4788-A04B-425721A8229C.jpeg" and [Value] <> "[07B89E2A-E06F-4B28-9652-DEBA10F45C8E.jpeg" and [Value] <> "[08CEDB6B-9F4D-4814-9B80-F730177C212F.jpeg" and [Value] <> "[0EFE191B-EE43-470F-B50D-27912737C7CE.jpeg" and [Value] <> "[0F8A3F18-CBBA-473D-9577-41256315914B.jpeg" and [Value] <> "[1023ADE9-31BE-4BA7-B3FF-1DB2E884F100.jpeg" and [Value] <> "[116C1DC7-E9CE-4639-97B1-33D565592AE5.jpeg" and [Value] <> "[1927BB8D-77DD-4452-88B5-E2EA4B9D4FB1.jpeg" and [Value] <> "[1D9C6E5F-7617-4891-9915-CE6A05615E29.jpeg" and [Value] <> "[1E214D6F-D3EA-4BE0-9D1B-9D1A3E496C2A.jpeg" and [Value] <> "[1FD43BD1-13AA-4675-9D3F-7518AE3593FB.jpeg" and [Value] <> "[2AED6A8A-651D-4D2F-AA89-19BC9BADBF52.jpeg" and [Value] <> "[30A6064C-9DFE-4895-9FE1-3FAC6AF9D949.jpeg" and [Value] <> "[315A08F6-9637-4066-BBC7-C096139D0226.jpeg" and [Value] <> "[31FDFB46-7F7F-4850-9235-D945A5601C66.jpeg" and [Value] <> "[32A1F266-3E63-4F43-B29C-DA20D1F37F1D.jpeg" and [Value] <> "[34AB4D97-69B1-4B5B-A82E-35F0D7776684.jpeg" and [Value] <> "[3AF058E7-C673-4673-AB97-B60B3C2A10F6.jpeg" and [Value] <> "[402DE44A-14F2-4FE0-9C09-989CBBF67DE0.jpeg" and [Value] <> "[49993120-F41B-457E-AA62-94ADFECD3555.jpeg" and [Value] <> "[5297D109-088B-4DB9-8F22-EE2760BADCFE.jpeg" and [Value] <> "[54C536E5-72EA-4294-A7CE-45817EBC50E0.jpeg" and [Value] <> "[5BCC6EEE-33B0-469D-B52A-5329115417F9.jpeg" and [Value] <> "[5D5EF7E2-7731-4DB1-9BBA-80CBFB8DA649.jpeg" and [Value] <> "[5EA84176-0712-4B8E-9243-A4556DBFE569.jpeg" and [Value] <> "[6C0AC767-4587-4FA3-9FEB-9B92AD3E2018.jpeg" and [Value] <> "[6CE93B8D-7B66-4436-84A5-FB55051FAECE.jpeg" and [Value] <> "[71CB1B9D-1128-420F-B153-ADCD1F3D9012.jpeg" and [Value] <> "[74030A4C-E47E-4945-BA22-89A84ED7EA5E.jpeg" and [Value] <> "[7694721E-E494-47A5-B3F6-7FA77D232F1A.jpeg" and [Value] <> "[837DA761-AF6B-40B3-BEF4-2EAEBA7612CB.jpeg" and [Value] <> "[8431F5FD-1C03-4A91-8144-AA32B4B6D4C2.jpeg" and [Value] <> "[8CF2D6A6-FD70-4D64-81E3-8F2B1B872474.jpeg" and [Value] <> "[8D727D9A-D226-439B-B16E-291052AA4D34.jpeg" and [Value] <> "[90F7CDE2-DADB-4CB0-A8C9-89803E9D543D.jpeg" and [Value] <> "[9103EEB8-FB59-471D-AEA9-CD6105057459.jpeg" and [Value] <> "[9B06647B-B311-45C8-BBBA-0BA890168344.jpeg" and [Value] <> "[9D514118-41C3-4E69-ACBE-DFE08A8C654B.jpeg" and [Value] <> "[9D76BADF-B963-4122-880B-D405DB516D89.jpeg" and [Value] <> "[A2F0133F-A9CE-49A6-845D-03E2C7247079.jpeg" and [Value] <> "[A6847169-1491-4685-8869-FC3E35394028.jpeg" and [Value] <> "[A85D5D05-EA5F-49A9-97E1-BAE4C0A6C3F6.jpeg" and [Value] <> "[A8F4BDA8-6E29-4162-8CAE-DEA2E2AEAE88.jpeg" and [Value] <> "[AC977129-678F-4F00-B31F-23F1E55B2F6A.jpeg" and [Value] <> "[AFC63CC9-E0D6-43FB-B718-6C1040337B22.jpeg" and [Value] <> "[B118BC33-301A-4069-955B-A0B353CC905D.jpeg" and [Value] <> "[B76131E2-8F2D-461F-B6D8-BFA40546822B.jpeg" and [Value] <> "[BB0104EA-D1FC-4E2E-BC20-47C88DB527C4.jpeg" and [Value] <> "[BC35B94A-BBED-434F-A3E9-2DC105E7F40B.jpeg" and [Value] <> "[BE7BA473-CB0A-425C-B969-B104E8AAC4E2.jpeg" and [Value] <> "[C483650E-86F1-42D1-8F91-1EF1B6397057.jpeg" and [Value] <> "[CC84374A-E25B-4825-8BC2-46EE3FE28BB0.jpeg" and [Value] <> "[CDAD34B6-9240-45C2-AE3C-651E3753B6C1.jpeg" and [Value] <> "[CFFEC350-372D-4181-ACE0-D3381532EB6F.jpeg" and [Value] <> "[D3D30C06-97A3-4C43-93C9-96E074F931BB.jpeg" and [Value] <> "[D8260F5A-B291-419C-9FB1-7C0A8556EBE5.jpeg" and [Value] <> "[DA16DABF-DF5B-4F4E-901A-3C0D2065F395.jpeg" and [Value] <> "[DA8A592C-8DBB-4DC1-9EE3-3E88D041744F.jpeg" and [Value] <> "[DBD03E11-8F13-40FE-AE7C-C0565CBB2A75.jpeg" and [Value] <> "[DEC9FA27-FB0A-434A-A0DD-6F9970FE0FA3.jpeg" and [Value] <> "[DF885DFF-4725-4C5A-B89A-303112D25F30.jpeg" and [Value] <> "[E017821A-8F98-4315-A0E9-10CCAC31B699.jpeg" and [Value] <> "[E6DAC59C-1E5F-45C6-A087-7E09979FF1A1.jpeg" and [Value] <> "[E9A09C3B-A4CB-464E-B9FE-74664FA7F966.jpeg" and [Value] <> "[E9CEDE8E-54A4-476B-8096-5100BF3BF572.jpeg" and [Value] <> "[EB7F928E-EFF4-493C-BD16-4D6A9010E1D4.jpeg" and [Value] <> "[ED1D1438-9754-427E-894B-254151AEFA45.jpeg" and [Value] <> "[F75CF6BD-B32D-4209-812D-4CF811392863.jpeg" and [Value] <> "[F9C0CF0F-0F0C-4B86-9EE0-6C8228EFA76F.jpeg" and [Value] <> "[FB3D784D-A032-4A4E-B7F0-94B7A7AF150F.jpeg" and [Value] <> "[image.jpg")),
#"Trimmed Text3" = Table.TransformColumns(#"Filtered Rows1",{{"Value", Text.Trim, type text}}),
#"Sorted Rows1" = Table.Sort(#"Trimmed Text3",{{"Attribute.1.1", Order.Descending}}),
#"Trimmed Text4" = Table.TransformColumns(#"Sorted Rows1",{{"Attribute.1.1", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text4",{{"Attribute.1.1", Text.Clean, type text}}),
#"Pivoted Column" = Table.Pivot(#"Cleaned Text", List.Distinct(#"Cleaned Text"[Attribute.1.1]), "Attribute.1.1", "Value")
in
#"Pivoted Column"

[v-frfei-msft]

Hi @Chanleakna123,

What is the data type of thie column? I cannot use your M code directly. Could you please share your pbix to me?

Regards,

Frank