cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jriedstra Frequent Visitor
Frequent Visitor

Combining two rows into one row (conditionally)

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Nathaniel_C Super Contributor
Super Contributor

Re: Combining two rows into one row (conditionally)

Hi @jriedstra ,

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

View solution in original post

4 REPLIES 4
Nathaniel_C Super Contributor
Super Contributor

Re: Combining two rows into one row (conditionally)

Hi @jriedstra ,

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

 

 

 

 

 

 

merge.PNG

 

 

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"
Nathaniel_C Super Contributor
Super Contributor

Re: Combining two rows into one row (conditionally)

Hi @jriedstra ,

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

View solution in original post

Nathaniel_C Super Contributor
Super Contributor

Re: Combining two rows into one row (conditionally)

Hope your brain recovers.Smiley Happy

jriedstra Frequent Visitor
Frequent Visitor

Re: Combining two rows into one row (conditionally)

Thanks for the quick response, let me have a look and I'll let you know 🙂 

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 72 members 983 guests
Please welcome our newest community members: