Splitting rows into separate rows on all columns in Power Query

Christoffer 0
Viewed 32

Instead of rows all my data is in one single row like this:

enter image description here

When I use #(if) as delimiter this happens:

enter image description here

I can do it again on another column, but that just duplicates the other rows again.

How do I split my data so it looks like this:

enter image description here

I hope you can point me in the right direction, thank you in advance.

Edit: Forgot to include the code:

let
    Source = Web.Page(Web.Contents("https://www.sparnord.dk/erhverv/produkter/likviditet-betalinger/basisrenter-paa-valutakonti/")),
    Data0 = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Promoted Headers", {{"Valuta", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Valuta"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Valuta", type text}})
in
    #"Changed Type1"
powerquery excel
1 Answers

assuming the character is #(lf), then try:

let Data = Web.Page(Web.Contents("https://www.sparnord.dk/erhverv/produkter/likviditet-betalinger/basisrenter-paa-valutakonti/")),
Source = Data{0}[Data],
//let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
TableTransform = Table.Combine(List.Transform(List.Transform(Table.ToRecords(Source), 
        (x) => List.Transform(Record.ToList(x), each Text.Split(_,"#(lf)"))), 
            each Table.FromColumns(_, Table.ColumnNames(Source))))
in TableTransform

enter image description here

horseyride0
Related