Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I am trying to find a solution for the following and am wrecking my brain so any help is appreciated, I have two tables that look as follows:
Table 1 - Required:
Name Exam Required
John English YES
John French YES
Table 2 - Obtained:
Name Exam Obtained
John English YES
John German YES
And what I want to end up with is this "combined" table:
Name Exam Required Obtained
John English YES YES note: the two rows have combined
John French YES <null>
John German <null> YES
Can anyone give me pointers? I am wrecking my brain trying to solve this.
Many thanks!
Jeroen
Solved! Go to Solution.
Hi @Anonymous ,
PBIX Obtained
Appended the query, transposed the table, reordered the columns, merged the columns, split the columns by position, removed columns, then transposed table, and did clean up.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hi @Anonymous ,
PBIX Obtained
Appended the query, transposed the table, reordered the columns, merged the columns, split the columns by position, removed columns, then transposed table, and did clean up.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hope your brain recovers.
Proud to be a Super User!
Thanks for the quick response, let me have a look and I'll let you know 🙂
Hi @Anonymous ,
Here is your table. Will attach the PBIX in a couple.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVbyS8xNVVBQcK1IzFVAAP+kksTMvNQUIBOsCkx45WfkgZTmpedkFmfAlEa6BiugAgwd7qlFuYl5Ckg6lGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Table 2 - Obtained:" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Table 2 - Obtained:", type text}}), #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Blank Rows", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name Exam Obtained ", type text}}), #"Split Column by Positions" = Table.SplitColumn(#"Changed Type1", "Name Exam Obtained ", Splitter.SplitTextByPositions({0, 7, 22}), {"Name Exam Obtained .1", "Name Exam Obtained .2", "Name Exam Obtained .3"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Positions",{{"Name Exam Obtained .1", type text}, {"Name Exam Obtained .2", type text}, {"Name Exam Obtained .3", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Name Exam Obtained .1", "Name"}, {"Name Exam Obtained .2", "Exam"}, {"Name Exam Obtained .3", "Obtained"}}), #"Appended Query" = Table.Combine({#"Renamed Columns", Required}), #"Transposed Table" = Table.Transpose(#"Appended Query"), #"Reordered Columns" = Table.ReorderColumns(#"Transposed Table",{"Column1", "Column3", "Column2", "Column4"}), #"Merged Columns" = Table.CombineColumns(#"Reordered Columns",{"Column1", "Column3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"), #"Split Column by Positions1" = Table.SplitColumn(#"Merged Columns", "Merged", Splitter.SplitTextByPositions({0, 7, 11}), {"Merged.1", "Merged.2", "Merged.3"}), #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Positions1",{{"Merged.1", type text}, {"Merged.2", type text}, {"Merged.3", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"Merged.2", "Merged.3"}), #"Transposed Table1" = Table.Transpose(#"Removed Columns") in #"Transposed Table1"
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.