Forum Discussion
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...
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 SplitColumnByDelimiterv2 - 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
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
Hello!
The script breaks after the sixth column, but changes all numerical values
Eg, column1 Content before: [[1435622400000
Content after: [[12755
To clarify, the example I created was just made up data with shortened values. Are you replacing my 'Source' line with your actual source?