Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
Solved! Go to 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
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
WOW!!
Amazing! 👏👏👏
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
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.
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!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
62 | |
40 | |
36 | |
28 | |
15 |