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. 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!
Solved! Go to 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"
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"
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.