Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I Have a simple table with one column
S. No.
1
2
3
4
5
7
8
9
I want to be able to detect missing values in this column (e.g. in this case 6).
Is this possible? Thanks! @amitchandak
Solved! Go to Solution.
You can create a virtual table of numbers with GENERATESERIES and then check for missing ones with the EXCEPT function (and then display the table of missing values with CONCATENATEX. Replace Number[Value] with your actual table/column name.
Missing Numbers =
VAR numbersincolumn =
DISTINCT ( Numbers[Value] )
VAR fulllistofnumbers =
GENERATESERIES ( 1, 9, 1 )
VAR missingnumbers =
EXCEPT ( fulllistofnumbers, numbersincolumn )
RETURN
CONCATENATEX ( missingnumbers, [Value], "," )
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @Anonymous ,
Actually I'm a little confused about your expected output.
In case you want to add the Full number as a column and then combine the original missing column as a new table, you could try this:
1. Create a new table:
New table = GENERATESERIES ( MIN('Table'[S.No.]), MAX('Table'[S.No.]), 1 )
2.Use LOOKUPVALUE() to get the matched original column:
original column = LOOKUPVALUE('Table'[S.No.],'Table'[S.No.],[Value])
The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You can create a virtual table of numbers with GENERATESERIES and then check for missing ones with the EXCEPT function (and then display the table of missing values with CONCATENATEX. Replace Number[Value] with your actual table/column name.
Missing Numbers =
VAR numbersincolumn =
DISTINCT ( Numbers[Value] )
VAR fulllistofnumbers =
GENERATESERIES ( 1, 9, 1 )
VAR missingnumbers =
EXCEPT ( fulllistofnumbers, numbersincolumn )
RETURN
CONCATENATEX ( missingnumbers, [Value], "," )
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
That works. Thanks! Is there a way to re-route the output to individual values in a new table's column?
Hi @Anonymous ,
Actually I'm a little confused about your expected output.
In case you want to add the Full number as a column and then combine the original missing column as a new table, you could try this:
1. Create a new table:
New table = GENERATESERIES ( MIN('Table'[S.No.]), MAX('Table'[S.No.]), 1 )
2.Use LOOKUPVALUE() to get the matched original column:
original column = LOOKUPVALUE('Table'[S.No.],'Table'[S.No.],[Value])
The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Great Thanks!
Hi @Anonymous ,
Could you please Accept my post as the solution as well if it could help you a little?
Thanks in advance!😀
Best Regards,
Eyelyn Qin
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |