Power Query with splitting columns but not repeating a certain column?

[Linksys45]

Hey everyone,

I was wondering if anyone could help with Power Query in Power BI on how I can achieve this. Below is the problem, basically the data as it is currently (it came from a data dump) has three challenges within one column, then solutions to those problems in three seperate columns. So solve 1 coordinates with the first answer in the challeneges section, and so on respectively. So for company A, problem a, solve 1 will always be the correct output. To make this data easier to harvest I want to see if there is a way to make it so it is repeating for each company but the problem and solution coordiante. So Company A's challenge for problem A will show in the column next to it in solve. I have put a basic table to illustrate how the data is now, and how I want it to show up in power BI/query when I put all the steps in.

I keep getting stuck because whenever I split the columns it will end in it repeating the solutions multiple times too. Any thoughts on if this is even possible?

Current						Want
Company	Three challenges	Solve 1	Solve 2	Solve 3		Company	Challenge	Solve
A	Problem A, Problem B, Problem C	abc	def	ghi		A	Problem A	abc
B	Problem F, Problem A, Problem G	jkl	mno	pqr		A	Problem B	def
C	Problem B, Problem A, Problem D	stu	vwx	yz		A	Problem C	ghi
						B	Problem F	jkl
						B	Problem A	mno
						B	Problem G	pqr
						C	Problem B	stu
						C	Problem A	vwx
						C	Problem D	yz

[Linksys45]

This worked (with a few tweaks as you mentioned while looking through my source). THANK YOU SO MUCH. It was killing me!

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQooyk/KSc1VcNRRgDGdEExnoIrEpGQgmZKaBiTTMzKVYnWilZyQdLohlCMZ4g5UkZWdAyRz8/KBZEFhEVinM5JOJ6w6XYAqiktKgWRZeQWQrKxSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, #"Three challenges" = _t, #"Solve 1" = _t, #"Solve 2" = _t, #"Solve 3" = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Three challenges", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Three challenges.1", "Three challenges.2", "Three challenges.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Company", type text}, {"Three challenges.1", type text}, {"Three challenges.2", type text}, {"Three challenges.3", type text}, {"Solve 1", type text}, {"Solve 2", type text}, {"Solve 3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Attribute.1]), "Attribute.1", "Value")
in
    #"Pivoted Column"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[Linksys45]

This worked (with a few tweaks as you mentioned while looking through my source). THANK YOU SO MUCH. It was killing me!