Web Datenquelle

Hi Spurgeon74, I've used sample from jgeddes.

This query should work with any number of columns. If you don't know how to use it - read note below my post.

v1

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wio42NDI3NVXSUTLUMzSwANLu+fkpQMo/GyJmZgaiDcz1LM1igSwk5QaWWJSbmICVm+mZWxoZoWswxa3BVM/Swjw2Vik2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t]),
    TransformedList = Table.ToList(Source, (x)=> 
        [ a = Text.Combine(x, "||"),
          b = Splitter.SplitTextByDelimiter("]")(a),
          c = List.Transform(b, (x)=> Text.Trim(x, {"|", "[", "]"}))
        ][c]),
    ToTable = Table.FromList(List.Select(TransformedList{0}?, (x)=> Text.Length(x) > 0)),
    SplitColumnByDelimiter = Table.SplitColumn(ToTable, "Column1", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv))
in
    SplitColumnByDelimiter

v2 - if you want to preserve column names:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wio42NDI3NVXSUTLUMzSwANLu+fkpQMo/GyJmZgaiDcz1LM1igSzaqo+NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Startdatum (UTC)" = _t, Wert = _t, Qualität = _t, Status = _t, Minimalwert = _t, Maximalwert = _t, #"Startdatum (UTC).1" = _t, Wert.1 = _t, Qualität.1 = _t, Status.1 = _t, Minimalwert.1 = _t, Maximalwert.1 = _t, #"Startdatum (UTC).2" = _t, Wert.2 = _t, Qualität.2 = _t, Status.2 = _t, Minimalwert.2 = _t, Maximalwert.2 = _t]),
    TransformedList = Table.ToList(Source, (x)=> 
        [ a = Text.Combine(x, "||"),
          b = Splitter.SplitTextByDelimiter("]")(a),
          c = List.Transform(b, (x)=> Text.Trim(x, {"|", "[", "]"}))
        ][c]),
    ToTable = Table.FromList(List.Select(TransformedList{0}?, (x)=> Text.Length(x) > 0)),
    SplitColumnByDelimiter = Table.SplitColumn(ToTable, "Column1", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), List.FirstN(Table.ColumnNames(Source), List.Count(Text.Split(ToTable{0}[Column1], "||"))))
in
    SplitColumnByDelimiter