2

I've got two data frames: zerowy_nazwa5, zatwierdzony_nazwa5,

and working 2 lines:

setdiff(zatwierdzony_nazwa5, zerowy_nazwa5)
setdiff(zerowy_nazwa5, zatwierdzony_nazwa5)

how I implement this in PowerBi?

Thanks for help

Terru_theTerror
  • 4,918
  • 2
  • 20
  • 39
Waldemar Malec
  • 25
  • 6
  • Can you expand your question? Do you have an R session and do you want to create a table in PowerBI with the data? Do you want to do something simulate setdiff on PowerBI without using R? ... – Juan Antonio Roldán Díaz Aug 29 '18 at 10:06
  • I want to transform my R script to PowerBi. Data import from R gives me two tables in PowerBi. I want to run setdiff on PowerBI on this two tables with using R function setdiff. – Waldemar Malec Aug 29 '18 at 10:15
  • From PowerBI you can not execute functions of R, you can use R to generate graphics and within the script you can do any operation, but you can not return the operations as tables. You have an option that is to run `setdiff` in the R script that generates your data.frame's and enter PowerBI 3 objects instead of two (where the third one is a data.frame with the two `sediff). – Juan Antonio Roldán Díaz Aug 29 '18 at 10:26
  • Ok. I do this in R. A <-setdiff(zatwierdzony_nazwa5, zerowy_nazwa5)but I wonder if it possible to this more dynamic using Power Bi – Waldemar Malec Aug 29 '18 at 10:49
  • I was thinking about making function in R and arguments of this function in PowerBi - but You write that is no possibile. – Waldemar Malec Aug 29 '18 at 10:56
  • It is only possible if the output is a plot of R, if you want to return data it is not possible. [See this.](https://powerbi.microsoft.com/fr-fr/blog/r-visuals-in-powerbi-service/) – Juan Antonio Roldán Díaz Aug 29 '18 at 11:07
  • Juan is correct. You cannot use R within a DAX measure. However, you can certainly compute a set difference using just DAX or M, so there's no need for R in this particular case. – Alexis Olson Aug 29 '18 at 17:18

2 Answers2

1

Reading your question, I'm assuming this:

  1. Your main goal is to do this internally in PowerBI
  2. You're not specifically asking how to do it using DAX

The Power of R in Power BI is not limited to R Visuals. You can load both single and multiple tables and use them as input to R scripts and any R functionality using Edit Queries > Transform > Run R Script.

Here's an example using two synthetic dataframes and setdiff():

Snippet 1 (from the dplyr::setdiff examples in R)

library(dplyr)
a <- data.frame(column = c(1:10, 10))
b <- data.frame(column = c(1:5, 5))
c <- dplyr::setdiff(a, b)

# Output
# column
# 1      6
# 2      7
# 3      8
# 4      9
# 5     10

Since you didn't describe your expected output, I'm assuming this is what you were after. But beware that if you're not using the dplyr library, base::setdiff() will give a different output:

Snippet 2

c <- base::setdiff(a, b)

# output

# column
# 1       1
# 2       2
# 3       3
# 4       4
# 5       5
# 6       6
# 7       7
# 8       8
# 9       9
# 10     10

And if you carefully follow the steps in this post you will be a able to end up with this in Power BI. But here's the essence of it: To reproduce the example, go to Edit Queries (Power Query Editor) > Enter Data and click OK. Then insert an R script using Transform > Run R script and insert the snippet above.

enter image description here

If anything is unclear, or if you're not able to reproduce the result, let me know.

vestland
  • 55,229
  • 37
  • 187
  • 305
  • Also works in text data: library(dplyr) a_text <- data.frame(column = c("A","B","C","D","E")) b_text <- data.frame(column = c("B","C","G")) c_text <- dplyr::setdiff(a_text, b_text) d_text <- dplyr::setdiff(b_text, a_text) ... sorry for formating – Waldemar Malec Aug 31 '18 at 21:22
1

Your question is rather unclear, so I'm going to have to make some assumptions. I will interpret your question as how to natively perform a set difference in Power BI.

Suppose we have tables A and B as follows

Table A:   Table B:      
Column     Column
------     ------
 1          2
 2          4
 3
 4
 5

and we want to get the set difference A - B

 Column
 ------
  1
  3
  5

You can do it in DAX or in the Power Query M language:

M language

You can do this using a left anti join. The M code looks like this:

 = Table.NestedJoin(A,{"Column"},B,{"Column"},"B",JoinKind.LeftAnti)

Delete the new "B" column and you're good to go.

Another way is to use the Table.SelectRows function:

= Table.SelectRows(A, each not List.Contains(B[Column], _[Column]))

DAX language

You just need to filter table A to exclude values in table B:

FILTER(A, NOT( A[Column] IN VALUES( B[Column] ) ) )

Or using the older CONTAINS syntax instead of IN:

FILTER(A, NOT( CONTAINS( VALUES( B[Column] ), B[Column], A[Column] ) ) )

Note: It certainly is possible to use R scripts within the Power Query environment, as vestland points out. It is not currently possible to use R scripts within a DAX expression, as Juan points out.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64