Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello Community,
I need help with finding missing items from a list of items, Below is my dataset and problem.
I have students dataset and i want to find the missing subjects for each student,
Student Data -
Subjects Table-
Requirement - I want to find the missing subjects for each student, Let's take Lyon for instance, Lyon had registered only one subject (Maths), Now i want to find he missing subjects for him from the subjects table i.e., Science, english, biology, physics.
My Result dataset should be,
How do i achieve the above result in power bi? Any suggestions would be appreciated.
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOztRRcM1L11EIyKhUitWJVjICivomlmToKDhl5oNFjCHqwGwTJD1gAVOocqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Subjects = _t]),
Subject = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7OVIrViVbyTSzJADNc89LBtFNmPpgOyKhUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Subject = _t]),
#"Added Column Missing" = let s = Subject[Subject] in Table.AddColumn(Source, "Missing", each let l = List.Transform(Text.Split([Subjects],","), Text.Trim) in Text.Combine(List.Difference(s, l), ", "))
in
#"Added Column Missing"
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Hi @Sam_BI_Analyst
You can achieve the desired results with those steps :
1. From PQ split the table of registered :
The result that you should get is :
2. create a relationship between this table the subjects table :
3. Create 2 Measures :
PBIX is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Hi @Sam_BI_Analyst
You can achieve the desired results with those steps :
1. From PQ split the table of registered :
The result that you should get is :
2. create a relationship between this table the subjects table :
3. Create 2 Measures :
PBIX is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOztRRcM1L11EIyKhUitWJVjICivomlmToKDhl5oNFjCHqwGwTJD1gAVOocqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Subjects = _t]),
Subject = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7OVIrViVbyTSzJADNc89LBtFNmPpgOyKhUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Subject = _t]),
#"Added Column Missing" = let s = Subject[Subject] in Table.AddColumn(Source, "Missing", each let l = List.Transform(Text.Split([Subjects],","), Text.Trim) in Text.Combine(List.Difference(s, l), ", "))
in
#"Added Column Missing"
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Can you explain what did you do for add column in the end
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |