Forum Discussion
Opposite Intersection
Hi mail2vjj,
>>Your solution almost works for me, however I need the result as a list rather than seperated by commas.
Current dax seems not support to generate this table, I'd like to suggest you to use power query to achieve your requirement.
Sample:
Full query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBPD4IgHIa/C2fZ+CNUx9K1Tq2784DazM10a3no2wca6E/RtSE6eHgfX5IExep9RwG6qqd5Xdq6qJpSf0UP1ZRmKdJA2b4+KA0SRCjWg+71+lE/lNjp3HZNf3JGnf6iIkDduqyu8oFhwMdWkhjwcWLRNcr4QmLRqY8v+2GPkAOhWBFyIORj2NQYOkj1RrN/WGaNWOYwsYXlDtvNlQIoGSEOhWFi2RJ7nCMWmyAmhri5VXqKYp9Wgqq/3/N4pacr3dAW5i6EK2Gh9As=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Name", type text}, {"Holding", Int64.Type}, {"Change", Int64.Type}, {"Category", type text}}),
#"Uppercased Text" = Table.TransformColumns(#"Changed Type1",{{"Name", Text.Upper, type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Uppercased Text",{"Date", "Name"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Date"}, {{"Name", each _[Name], type list}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Excepted", each Function.Invoke((current as list, previous as list) =>
List.Difference(current,previous),{#"Added Index"{[Index]}[Name],#"Added Index"{[Index]-1}[Name]})),
#"Removed Operation" = Table.RemoveRowsWithErrors(Table.RemoveColumns(#"Added Custom",{"Name", "Index"}), {"Excepted"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Operation", "Excepted")
in
#"Expanded Custom"
Rgards,
Xiaoxin Sheng
Hi Anonymous
I tried to use your code to create a new query with my file but it gave me an error.
Here is the screenshot of the error. Maybe it might help you understand where am I going wrong here.
I am not really familiar to the advanced editor in the query.
So if you could please modify your code according to my file, that way I should be able to get a better understanding of your code and how it works.
Here is the link to the file in my one drive.
https://1drv.ms/x/s!Ap0qSKP-4qpThBkZj5kXIu1RAoSJ
And my apologies for the late response.
Thank you,
Vishesh Jain
HI mail2vjj,
Maybe you can take a look at below sample, I modify the formula to use sheet result as reference.(you need to go to query editor and relocate your excel file)
Regards,
Xiaoxin Sheng
Anonymous
Thank you for replying.
I was going over your solution and saw some steps like 'Grouped Rows' and you added a custom column as well.
I wanted to know if these steps are readily available in Power BI or did you have to write a custom code for it.
I am trying to fix your solution into my actual file but for some reason it is not working, even after I changed the 'Source' in the first line of your code to my existing table.
I'll let you know as soon as I a figure that out.
Thank you again for all your help.
Vishesh Jain
Hi Anonymous
Your solution works!
I had to make a couple of tweaks to it, like change the name of file in the code and add the name of the columns.
The biggest change I had to make, though I didn't really understand why, was to move the 'Date' and 'Name' columns to the beginning of the table.
After that your solution worked but only for a table that already exists. I tried to use it with an Appended Table and it gave me an error.
However, after everything it is working now.
If you could please post your solution with the zip file again and mention the tweaks that I made, then it would help other people in the future.
Thank you so much for your help.
Vishesh Jain