Select Columns contains errors

[Anonymous]

Hi,

I want to select all the columns that at least 1 row contain an error,

I already used Table.SelectRowsWithErrors to select the rows with errors.

My end goal is to create a table that will contain all the value level errors from the given table,

Example:

QueryName	Reason	Message	Detail
Query1	DataFormat.Error	We couldn't convert to Number.	1
Query2	DataFormat.Error	We couldn't parse the input provided as a Date value.	some error

[Jimmy801 · Accepted solution]

Hello @Anonymous 

This solution should give you a perfect overview in witch Row and Column the error occured, with all details of the reason.

let
	Source = #table 
	(
		{"TaskList SignOff Date and Time","TaskList SignOff Date and Time2"},
		{ {"43849,4374997106","43849,4374997106"}, {"43849,4583329861","43849,4583329861"}, {"43849,4791662616","43849,4791662616"}, {"43849,499999537","43849,499999537"}, {"43849,5208328125","43849,5208328125"}, {"43849,541666088","43849,541666088"}, {"43849,5624993634","43849,5624993634"}, {"43849,5833326389","43849,5833326389"}, {"43849,6041659144","43849,6041659144"}, {"43849,6249991898","43849,6249991898"}, {"43849,6458324653","43849,6458324653"}, {"43849,6666657407","43849,6666657407"}, {"43849,6874990162","43849,6874990162"}, {"43849,7083322917","43849,7083322917"}, {"43849,7291655671","43849,7291655671"}, {"43849,7499988426","43849,7499988426"}, {"43849,7708321181","43849,7708321181"}, {"43849,7916653935","43849,7916653935"}, {"43849,812498669","43849,812498669"}, {"abc","abc"}, {"tets","tets"}, {"dfs","dfs"}, {"sdf","sdf"}, {"sdfs","sdfs"} } 
	),
	AddedIndex = Table.AddIndexColumn
	(
		Source,
		"Index",
		1,
		1
	),
	ChangeType = Table.TransformColumnTypes
	(
		AddedIndex,
		{{"TaskList SignOff Date and Time", Int64.Type}}
	),
	GroupAndTranspose = Table.Group
	(
		ChangeType,
		{"Index"},
		{{"AllRows", each Table.FromColumns( {List.Difference( Table.ColumnNames(_),{"Index"})}& Table.ToColumns( Table.Transpose(Table.RemoveColumns(_,"Index"),{"Value"})),{"ColumnName", "Value"}), type table [TaskList SignOff Date and Time=number, TaskList SignOff Date and Time2=text]}}
	),
	ExpandAllRows = Table.ExpandTableColumn
	(
		GroupAndTranspose,
		"AllRows",
		{"ColumnName", "Value"}
	),
	CheckError = Table.AddColumn
	(
		ExpandAllRows,
		"Benutzerdefiniert",
		each try [Value]
	),
	Expand = Table.ExpandRecordColumn
	(
		CheckError,
		"Benutzerdefiniert",
		{"HasError", "Value", "Error"},
		{"HasError", "Value.1", "Error"}
	),
	FilterForError = Table.SelectRows
	(
		Expand,
		each 
		(
			[HasError] = true
		)
	),
	ExpandError = Table.ExpandRecordColumn
	(
		FilterForError,
		"Error",
		{"Reason", "Message", "Detail"},
		{"Reason", "Message", "Detail"}
	)
 in 
	ExpandError

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

WOW!!

[mr_arodrigues]

Amazing! 👏👏👏

[Anonymous]

Thanks, great solution.

I wanted to ask what is the purpose of the code below:

ChangeType,
		{"Index"},
		{{"AllRows", each Table.FromColumns( {List.Difference( Table.ColumnNames(_),{"Index"})}& Table.ToColumns( Table.Transpose(Table.RemoveColumns(_,"Index"),{"Value"})),{"ColumnName", "Value"}), type table [TaskList SignOff Date and Time=number, TaskList SignOff Date and Time2=text]}}

 "type table [TaskList SignOff Date and Time=number, TaskList SignOff Date and Time2=text]"

after i changed the source table, even if this line remains unchanged i get the result i expected.

[Jimmy801]

Hello @Anonymous 

this does a type definition of the table after the grouping. You can easily cancel it. This is created by the UI when you do a grouping, using All Rows. 

Thanks for the feedback

Jimmy

[edhans]

• Select all of the columns in a given query
• Unpivot columns
• Filter the Values field for errors only, or Keep Errors on the whole table.
• Add a new column with the following formula
  • try [Values]
• Expand both columns and you'll get a HasError column (which should all be true if you only have errors), and another record column called Errors
• Expand the Errors column. It will give you three new columns, Reason, Message, and Detail. Detail is the data in there causing the error.

That will give you a list of all fields in that query with an error, what the error is, and what is causing it.

I think you'll have to do this on a query by query basis unless you want to get into a more sophisticated custom function that would loop through all of your queries.

[FireFighter1017]

@edhans , your reply is much more usefull than Jimmy801's answer even though his/her answer is working perfectly fine, I found it difficult to integrate into my code.

Yours made sooo simple to integrate!!  Thanks a lot!