Forum Discussion
Solved
Create new table applying cartesian logic
Hello all, I have 3 tables, 2 of them are dimensional: DIM TABLE - EXAMS exam1 exam2 exam3 DIM TABLE - STUDENTS student1 student2 student3 FACT TABLE - RESULTS ...
jeronimo2334 Updated to meet that additional requirement
Place the following M code in a blank query to see the steps for your expected table.
See it all at work in the attached file.
let Source = ExamsT, #"Added Custom" = Table.AddColumn(Source, "Custom", each StudentsT), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Student"}, {"Student"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Student", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Exam", "Student"}, ResultsT, {"Exam", "Student"}, "ResultsT", JoinKind.LeftOuter), #"Expanded ResultsT" = Table.ExpandTableColumn(#"Merged Queries", "ResultsT", {"Result"}, {"Result"}), #"Replaced Value" = Table.ReplaceValue(#"Expanded ResultsT",null,"Not taken",Replacer.ReplaceValue,{"Result"}), #"Sorted Rows" = Table.Buffer(Table.Sort(#"Replaced Value",{{"Exam", Order.Ascending}, {"Student", Order.Ascending}, {"Result", Order.Descending}})), #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Exam", "Student"}) in #"Removed Duplicates"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Correct.
jeronimo2334 Updated to meet that additional requirement
Place the following M code in a blank query to see the steps for your expected table.
See it all at work in the attached file.
let
Source = ExamsT,
#"Added Custom" = Table.AddColumn(Source, "Custom", each StudentsT),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Student"}, {"Student"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Student", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Exam", "Student"}, ResultsT, {"Exam", "Student"}, "ResultsT", JoinKind.LeftOuter),
#"Expanded ResultsT" = Table.ExpandTableColumn(#"Merged Queries", "ResultsT", {"Result"}, {"Result"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded ResultsT",null,"Not taken",Replacer.ReplaceValue,{"Result"}),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Replaced Value",{{"Exam", Order.Ascending}, {"Student", Order.Ascending}, {"Result", Order.Descending}})),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Exam", "Student"})
in
#"Removed Duplicates"
|
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Thank you that worked. The pbix seems to be from some other post but the M query is right.
Corrected the pbix