Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Select Columns contains errors

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
Query1DataFormat.ErrorWe couldn't convert to Number.1
Query2DataFormat.ErrorWe couldn't parse the input provided as a Date value.some error
1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

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

View solution in original post

7 REPLIES 7
Jimmy801
Community Champion
Community Champion

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
Not applicable

WOW!!

Amazing! 👏👏👏

Anonymous
Not applicable

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.

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
Super User
Super User

  • 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@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!

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors