Forum Discussion

Spurgeon74's avatar
Spurgeon74
Frequent Visitor
2 years ago
Solved

Web Datenquelle

Hallo!   Ich möchte Messdaten aus einem System laden. Das Problem ist, dass die Importdaten in einer Zeile importiert werden und man hier schnell das Limit erreicht. Bsp.: Startdatum1 (UTC), Wer...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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
jgeddes's avatar
jgeddes
Super User
2 years ago

This strategy from goodly may help you. Unstacking Rows into Columns 

In order to use this strategy you would need to first transpose your row into a single column and also it requires that the split occurs every 6 columns.

Here is a quick M code example.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wio42NDI3NVXSUTLUMzSwANLu+fkpQMo/GyJmZgaiDcz1LM1igSwk5QaWWJSbmICVm+mZWxoZoWswxa3BVM/SwjxWKTYWAA==", 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]),
    #"Transposed Table" = Table.Transpose(Source)[Column1],
    Custom1 = List.Split(#"Transposed Table", 6),
    Custom2 = List.Transform(Custom1, each Record.FromList(_, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"})),
    Custom3 = Table.FromRecords(Custom2)
in
    Custom3
Spurgeon74's avatar
Spurgeon74
Frequent Visitor
2 years ago

Hello!

The script breaks after the sixth column, but changes all numerical values

Eg, column1 Content before: [[1435622400000

Content after: [[12755