Power BI. Remove duplicates but keep null values
1
In Power BI, I need to remove duplicates of a column but keep the null values as they are 'pending'. Is there a way I can do it with DAX or the Query Editor?
powerbi dax powerquery m
edited Nov 15 '18 at 17:04
Alexis Olson
14.6k21834
asked Nov 15 '18 at 8:04
deoosdeoos
174
add a comment |
1
In Power BI, I need to remove duplicates of a column but keep the null values as they are 'pending'. Is there a way I can do it with DAX or the Query Editor?
powerbi dax powerquery m
edited Nov 15 '18 at 17:04
Alexis Olson
14.6k21834
asked Nov 15 '18 at 8:04
deoosdeoos
174
add a comment |
1
1
1
1
In Power BI, I need to remove duplicates of a column but keep the null values as they are 'pending'. Is there a way I can do it with DAX or the Query Editor?
powerbi dax powerquery m
edited Nov 15 '18 at 17:04
Alexis Olson
14.6k21834
asked Nov 15 '18 at 8:04
deoosdeoos
174
In Power BI, I need to remove duplicates of a column but keep the null values as they are 'pending'. Is there a way I can do it with DAX or the Query Editor?
powerbi dax powerquery m
powerbi dax powerquery m
edited Nov 15 '18 at 17:04
Alexis Olson
14.6k21834
asked Nov 15 '18 at 8:04
deoosdeoos
174
edited Nov 15 '18 at 17:04
Alexis Olson
14.6k21834
asked Nov 15 '18 at 8:04
deoosdeoos
174
edited Nov 15 '18 at 17:04
Alexis Olson
14.6k21834
edited Nov 15 '18 at 17:04
Alexis Olson
14.6k21834
edited Nov 15 '18 at 17:04
Alexis Olson
14.6k21834
14.6k21834
asked Nov 15 '18 at 8:04
deoosdeoos
174
asked Nov 15 '18 at 8:04
deoosdeoos
174
asked Nov 15 '18 at 8:04
deoosdeoos
174
174
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
2
Filter the table in two ways, without nulls and only nulls.
On the table without nulls, remove duplicates. Home > Remove Rows > Remove Duplicates
Append the null rows to this table.
The M code will look like this:
let
Source = <Data source or previous step reference here>,
AllNulls = Table.SelectRows(Source, each ([Column1] = null)),
NoNulls = Table.SelectRows(Source, each ([Column1] <> null)),
#"Removed Duplicates" = Table.Distinct(NoNulls),
#"Appended Query" = Table.Combine(#"Removed Duplicates", AllNulls)
in
#"Appended Query"
answered Nov 15 '18 at 17:04
Alexis OlsonAlexis Olson
14.6k21834
add a comment |
1
Try this in the Query Editor.
- Add an index column (Add Collumn tab > Index Column)
- Add a Custom Column with this formula ([Test] is your original column with nulls and duplicates.
- Rightclick the latest column [Temp] and select
Remove Duplicates - Remove [Index] and [Temp] columns
answered Nov 15 '18 at 15:01
Marco VosMarco Vos
1,998149
The one thing you need to be careful about with this if your[Test]column has a null in row N and the column also contains the value N in another row, then one of those rows gets removed when deleting duplicates.
– Alexis Olson
Nov 15 '18 at 17:07
@Alexis Olson. You're right off course. I made my solution more robust, by adding some random characters to the index value. See edit.
– Marco Vos
Nov 16 '18 at 8:27
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53314845%2fpower-bi-remove-duplicates-but-keep-null-values%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
Filter the table in two ways, without nulls and only nulls.
On the table without nulls, remove duplicates. Home > Remove Rows > Remove Duplicates
Append the null rows to this table.
The M code will look like this:
let
Source = <Data source or previous step reference here>,
AllNulls = Table.SelectRows(Source, each ([Column1] = null)),
NoNulls = Table.SelectRows(Source, each ([Column1] <> null)),
#"Removed Duplicates" = Table.Distinct(NoNulls),
#"Appended Query" = Table.Combine(#"Removed Duplicates", AllNulls)
in
#"Appended Query"
answered Nov 15 '18 at 17:04
Alexis OlsonAlexis Olson
14.6k21834
add a comment |
2
Filter the table in two ways, without nulls and only nulls.
On the table without nulls, remove duplicates. Home > Remove Rows > Remove Duplicates
Append the null rows to this table.
The M code will look like this:
let
Source = <Data source or previous step reference here>,
AllNulls = Table.SelectRows(Source, each ([Column1] = null)),
NoNulls = Table.SelectRows(Source, each ([Column1] <> null)),
#"Removed Duplicates" = Table.Distinct(NoNulls),
#"Appended Query" = Table.Combine(#"Removed Duplicates", AllNulls)
in
#"Appended Query"
answered Nov 15 '18 at 17:04
Alexis OlsonAlexis Olson
14.6k21834
add a comment |
2
2
2
Filter the table in two ways, without nulls and only nulls.
On the table without nulls, remove duplicates. Home > Remove Rows > Remove Duplicates
Append the null rows to this table.
The M code will look like this:
let
Source = <Data source or previous step reference here>,
AllNulls = Table.SelectRows(Source, each ([Column1] = null)),
NoNulls = Table.SelectRows(Source, each ([Column1] <> null)),
#"Removed Duplicates" = Table.Distinct(NoNulls),
#"Appended Query" = Table.Combine(#"Removed Duplicates", AllNulls)
in
#"Appended Query"
answered Nov 15 '18 at 17:04
Alexis OlsonAlexis Olson
14.6k21834
Filter the table in two ways, without nulls and only nulls.
On the table without nulls, remove duplicates. Home > Remove Rows > Remove Duplicates
Append the null rows to this table.
The M code will look like this:
let
Source = <Data source or previous step reference here>,
AllNulls = Table.SelectRows(Source, each ([Column1] = null)),
NoNulls = Table.SelectRows(Source, each ([Column1] <> null)),
#"Removed Duplicates" = Table.Distinct(NoNulls),
#"Appended Query" = Table.Combine(#"Removed Duplicates", AllNulls)
in
#"Appended Query"
answered Nov 15 '18 at 17:04
Alexis OlsonAlexis Olson
14.6k21834
answered Nov 15 '18 at 17:04
Alexis OlsonAlexis Olson
14.6k21834
answered Nov 15 '18 at 17:04
Alexis OlsonAlexis Olson
14.6k21834
answered Nov 15 '18 at 17:04
Alexis OlsonAlexis Olson
14.6k21834
14.6k21834
add a comment |
add a comment |
1
Try this in the Query Editor.
- Add an index column (Add Collumn tab > Index Column)
- Add a Custom Column with this formula ([Test] is your original column with nulls and duplicates.
- Rightclick the latest column [Temp] and select
Remove Duplicates - Remove [Index] and [Temp] columns
answered Nov 15 '18 at 15:01
Marco VosMarco Vos
1,998149
The one thing you need to be careful about with this if your[Test]column has a null in row N and the column also contains the value N in another row, then one of those rows gets removed when deleting duplicates.
– Alexis Olson
Nov 15 '18 at 17:07
@Alexis Olson. You're right off course. I made my solution more robust, by adding some random characters to the index value. See edit.
– Marco Vos
Nov 16 '18 at 8:27
add a comment |
1
Try this in the Query Editor.
- Add an index column (Add Collumn tab > Index Column)
- Add a Custom Column with this formula ([Test] is your original column with nulls and duplicates.
- Rightclick the latest column [Temp] and select
Remove Duplicates - Remove [Index] and [Temp] columns
answered Nov 15 '18 at 15:01
Marco VosMarco Vos
1,998149
The one thing you need to be careful about with this if your[Test]column has a null in row N and the column also contains the value N in another row, then one of those rows gets removed when deleting duplicates.
– Alexis Olson
Nov 15 '18 at 17:07
@Alexis Olson. You're right off course. I made my solution more robust, by adding some random characters to the index value. See edit.
– Marco Vos
Nov 16 '18 at 8:27
add a comment |
1
1
1
Try this in the Query Editor.
- Add an index column (Add Collumn tab > Index Column)
- Add a Custom Column with this formula ([Test] is your original column with nulls and duplicates.
- Rightclick the latest column [Temp] and select
Remove Duplicates - Remove [Index] and [Temp] columns
answered Nov 15 '18 at 15:01
Marco VosMarco Vos
1,998149
Try this in the Query Editor.
- Add an index column (Add Collumn tab > Index Column)
- Add a Custom Column with this formula ([Test] is your original column with nulls and duplicates.
- Rightclick the latest column [Temp] and select
Remove Duplicates - Remove [Index] and [Temp] columns
answered Nov 15 '18 at 15:01
Marco VosMarco Vos
1,998149
edited Nov 16 '18 at 8:24
answered Nov 15 '18 at 15:01
Marco VosMarco Vos
1,998149
answered Nov 15 '18 at 15:01
Marco VosMarco Vos
1,998149
answered Nov 15 '18 at 15:01
Marco VosMarco Vos
1,998149
1,998149
The one thing you need to be careful about with this if your[Test]column has a null in row N and the column also contains the value N in another row, then one of those rows gets removed when deleting duplicates.
– Alexis Olson
Nov 15 '18 at 17:07
@Alexis Olson. You're right off course. I made my solution more robust, by adding some random characters to the index value. See edit.
– Marco Vos
Nov 16 '18 at 8:27
add a comment |
The one thing you need to be careful about with this if your[Test]column has a null in row N and the column also contains the value N in another row, then one of those rows gets removed when deleting duplicates.
– Alexis Olson
Nov 15 '18 at 17:07
@Alexis Olson. You're right off course. I made my solution more robust, by adding some random characters to the index value. See edit.
– Marco Vos
Nov 16 '18 at 8:27
The one thing you need to be careful about with this if your
[Test] column has a null in row N and the column also contains the value N in another row, then one of those rows gets removed when deleting duplicates.– Alexis Olson
Nov 15 '18 at 17:07
The one thing you need to be careful about with this if your
[Test] column has a null in row N and the column also contains the value N in another row, then one of those rows gets removed when deleting duplicates.– Alexis Olson
Nov 15 '18 at 17:07
@Alexis Olson. You're right off course. I made my solution more robust, by adding some random characters to the index value. See edit.
– Marco Vos
Nov 16 '18 at 8:27
@Alexis Olson. You're right off course. I made my solution more robust, by adding some random characters to the index value. See edit.
– Marco Vos
Nov 16 '18 at 8:27
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53314845%2fpower-bi-remove-duplicates-but-keep-null-values%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
