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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

6 REPLIES 6
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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors