Sorry if my formating is off, I'm new to powerbi. So the general premise of my problem is that I have a folder with a bunch of CSVs that I want to compare to one main csv, and then generate some graphs showing.
I have 2 main problems.
1. So specifically, all CSVs have only 4 relevant columns, so basically I'm using powerquery function each time to generate the tables
(input as table) =>
let
relevant = Table.SelectColumns(input, {"acc","env","server","compliance"}),
filtered = Table.SelectRows(relevant, each [d] = "pending")
in
filtered
and then I plan on somehow eventually running this on each csv in the folder eventually to generate filtered CSVs. I'm a little confused on how to do this, I'm not sure how to link powerquery with powerbi, and then from there, how am I able to pass it into powerbi to be used as variables into my second problem.
2. So for generating a graphic. Essentially, for each filtered CSV, I want to use INTERSECT and EXCEPT with each CSV against the main CSV and then use COUNTROWS on it to generate a graphing table, ie
| Month | Intersect Count | Except Count |
| Jan | 100 | 0 |
| Feb | 80 | 20 |
| Mar | 70 | 30 |
| Apr | 80 | 20 |
So basically the January CSV is the main one, January intersected with itself is itself.
Is there a way to run INTERCEPT and EXCEPT on some variable number of CSVs to put into this table? What would be the DAX for it.
Right now, I have something like this
Graphic =
SELECTCOLUMNS(
{
(Jan, COUNTROWS(INTERSECT(Jan,Jan),COUNTROWS(EXCEPT(Jan,Jan)),
(Feb, COUNTROWS(INTERSECT(Jan,Feb),COUNTROWS(EXCEPT(Jan,Feb)),
(Mar, COUNTROWS(INTERSECT(Jan,Mar),COUNTROWS(EXCEPT(Jan,Mar)),
(Apr, COUNTROWS(INTERSECT(Jan,Apr),COUNTROWS(EXCEPT(Jan,Apr))
},
"Month", [Value1],
"Pending",[Value2],
"Removed", [Value3
)
this is then shown as a stacked bar graph. I am manually putting in rows 1 by 1, right now this is fine I was just wondering if it could be automated could be used dynamically. Any advice on my code would be helpful too.
