Split column by value in row

[KristianVangen]

Hi. Cannot figure out what to look for here, so this question is probably answered somewhere else.

I have data in a .txt file structured like this

Column1:

ValueX

--- --- --- ---

Column2:

Value1

--- --- --- ---

Column1:

ValueY

--- --- --- ---

Column2:

Value2

Value3

--- --- --- ---

Column1:

ValueZ

--- --- --- ---

Column2:

Value4

Value5

Value1

--- --- --- ---

My first column only has one value each time, but my second column can have multiple values. And the values in the second column can repeat. I need to get this into something like this:

Column1	Column2
ValueX	Value1
ValueY	Value2
ValueY	Value3
ValueZ	Value4
ValueZ	Value5
ValueZ	Value1

How can I manage this?

Thanks in advance!

[latimeria · Accepted solution]

Hi @KristianVangen ,

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkvMKU2NUNKBMAyVYnWgYpEwMSOwGIxnjFARBRMzQVFhisIDmhgLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1"})
in
    #"Filled Down"