Styjun

What is this "opened" cube called?

Is "survival" paracord with fire starter strand dangerous

“I hope he visit us more often” Why is this wrong?

How to understand payment due date for credit card?

Is it good practice to speed up and slow down where not written in a song?

Is there an in-universe explanation given to the senior Imperial Navy Officers as to why Darth Vader serves Emperor Palpatine?

Under GDPR, can I give permission once to allow everyone to store and process my data?

Why are JWST optics not enclosed like HST?

Create a list of snaking numbers under 50,000

In Endgame, wouldn't Stark have remembered Hulk busting out of the stairwell?

What checks exist against overuse of presidential pardons in the USA?

Necessity of tenure for lifetime academic research

Do multi-engine jets need all engines with equal age to reduce asymmetry in thrust and fuel consumption arising out of deterioration?

Group riding etiquette

Do universities maintain secret textbooks?

Is it recommended to point out a professor's mistake during their lecture?

'Horseshoes' for Deer?

What is the practical impact of using System.Random which is not cryptographically random?

How did medieval manors handle population growth? Was there room for more fields to be ploughed?

In what language did Túrin converse with Mím?

is "prohibition against," a double negative?

How can I fix cracks between the bathtub and the wall surround?

Idiomatic way to create an immutable and efficient class in C++?

How to differentiate between two people with the same name in a story?

How can I find rows from an Order table that do not have matching LineItem rows using Power BI?

In power query language(M language) how can we add custom “value” and “table” columns to a table manually?Find String from One List within Another List and Return String FoundHow to transpose Day of Month columns to rows in a table?Access SQL/Power Query - Matching TablesClean up table in Power BIPower Query - Data Transformation from a single column to a whole tablePower BI/DAX Query - Finding value against range on another tableHow do I calculated group averages with a filter in DAX?Fill in Missing Dates Power BIAdd column of previous values from table of tables in Power BI / Power Query

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I have two tables and one of them has orphan records. I'm just starting to dive into Power BI. I know how to solve this problem using SQL Server and I assume it's not that hard to solve in Power BI. However, Power BI has me stumped at the moment. Using Power BI, how can I find rows from an Order table that do not have matching LineItem rows?

This is what the solution would be in SQL. I'm trying to solve this without leaving Power BI.

SELECT DISTINCT 
 [Order].[OrderId]
FROM 
 [dbo].[Order]
 LEFT JOIN [dbo].[LineItem] ON [Order].OrderId = [LineItem].[OrderId]
WHERE 
 [LineItem].[OrderId] IS NULL

powerbi dax powerquery m

share|improve this question

edited Mar 27 at 22:40

Tarzan

asked Mar 27 at 22:08

TarzanTarzan

2,60422 gold badges3434 silver badges5555 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Is that LineItem a column on another table? Could you provide some sample data? And maybe desired output? Power BI is very flexible, with M and DAX, so there are multiple ways to do it.
  
  – mxix
  Mar 27 at 22:14
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  LineItem is another table. The desired output is a list of OrderIDs without matching LineItem rows.
  
  – Tarzan
  Mar 27 at 22:23
  
  

  
  
  
  

add a comment | 

0

I have two tables and one of them has orphan records. I'm just starting to dive into Power BI. I know how to solve this problem using SQL Server and I assume it's not that hard to solve in Power BI. However, Power BI has me stumped at the moment. Using Power BI, how can I find rows from an Order table that do not have matching LineItem rows?

This is what the solution would be in SQL. I'm trying to solve this without leaving Power BI.

SELECT DISTINCT 
 [Order].[OrderId]
FROM 
 [dbo].[Order]
 LEFT JOIN [dbo].[LineItem] ON [Order].OrderId = [LineItem].[OrderId]
WHERE 
 [LineItem].[OrderId] IS NULL

powerbi dax powerquery m

share|improve this question

edited Mar 27 at 22:40

Tarzan

asked Mar 27 at 22:08

TarzanTarzan

2,60422 gold badges3434 silver badges5555 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Is that LineItem a column on another table? Could you provide some sample data? And maybe desired output? Power BI is very flexible, with M and DAX, so there are multiple ways to do it.
  
  – mxix
  Mar 27 at 22:14
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  LineItem is another table. The desired output is a list of OrderIDs without matching LineItem rows.
  
  – Tarzan
  Mar 27 at 22:23
  
  

  
  
  
  

add a comment | 

I have two tables and one of them has orphan records. I'm just starting to dive into Power BI. I know how to solve this problem using SQL Server and I assume it's not that hard to solve in Power BI. However, Power BI has me stumped at the moment. Using Power BI, how can I find rows from an Order table that do not have matching LineItem rows?

This is what the solution would be in SQL. I'm trying to solve this without leaving Power BI.

SELECT DISTINCT 
 [Order].[OrderId]
FROM 
 [dbo].[Order]
 LEFT JOIN [dbo].[LineItem] ON [Order].OrderId = [LineItem].[OrderId]
WHERE 
 [LineItem].[OrderId] IS NULL

powerbi dax powerquery m

share|improve this question

edited Mar 27 at 22:40

Tarzan

asked Mar 27 at 22:08

TarzanTarzan

2,60422 gold badges3434 silver badges5555 bronze badges

SELECT DISTINCT 
 [Order].[OrderId]
FROM 
 [dbo].[Order]
 LEFT JOIN [dbo].[LineItem] ON [Order].OrderId = [LineItem].[OrderId]
WHERE 
 [LineItem].[OrderId] IS NULL

share|improve this question

edited Mar 27 at 22:40

Tarzan

asked Mar 27 at 22:08

TarzanTarzan

2,60422 gold badges3434 silver badges5555 bronze badges

share|improve this question

edited Mar 27 at 22:40

Tarzan

asked Mar 27 at 22:08

TarzanTarzan

2,60422 gold badges3434 silver badges5555 bronze badges

asked Mar 27 at 22:08

TarzanTarzan

2,60422 gold badges3434 silver badges5555 bronze badges

asked Mar 27 at 22:08

TarzanTarzan

2,60422 gold badges3434 silver badges5555 bronze badges

1 Answer
1

active

oldest

votes

1

To resolve it with M, in your Power Query Editor.

Load both tables, and apply a Merge operation.

Choose both tables and key columns and pick

Left Anti (only rows on the first table) option for Join Kind

That should outcome rows that have no match on the second table.

share|improve this answer

answered Mar 27 at 22:30

mxixmxix

2,64199 silver badges1616 bronze badges

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55387224%2fhow-can-i-find-rows-from-an-order-table-that-do-not-have-matching-lineitem-rows%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

To resolve it with M, in your Power Query Editor.

Load both tables, and apply a Merge operation.

Choose both tables and key columns and pick

Left Anti (only rows on the first table) option for Join Kind

That should outcome rows that have no match on the second table.

share|improve this answer

answered Mar 27 at 22:30

mxixmxix

2,64199 silver badges1616 bronze badges

add a comment | 

1

To resolve it with M, in your Power Query Editor.

Load both tables, and apply a Merge operation.

Choose both tables and key columns and pick

Left Anti (only rows on the first table) option for Join Kind

That should outcome rows that have no match on the second table.

share|improve this answer

answered Mar 27 at 22:30

mxixmxix

2,64199 silver badges1616 bronze badges

add a comment | 

To resolve it with M, in your Power Query Editor.

Load both tables, and apply a Merge operation.

Choose both tables and key columns and pick

Left Anti (only rows on the first table) option for Join Kind

That should outcome rows that have no match on the second table.

share|improve this answer

answered Mar 27 at 22:30

mxixmxix

2,64199 silver badges1616 bronze badges

share|improve this answer

answered Mar 27 at 22:30

mxixmxix

2,64199 silver badges1616 bronze badges

answered Mar 27 at 22:30

mxixmxix

2,64199 silver badges1616 bronze badges

answered Mar 27 at 22:30

mxixmxix

2,64199 silver badges1616 bronze badges