Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |