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.
I have a use case in which I'm getting the data in rather unformatted way. However it can be tackled by understanding the structure. Kind of a "you just need to know" way, not ideal I know.
But this has brought me a challenge that I have a list of data i.e.
12312,
1231235,
540945,
4506,
2349,
2349,
13409,
3490834,
1349,
3490835,
309034,
2394,
110,
124349,
...
What I need to achieve with this list is to create a table out of it and as an example the above example needs to be formatted to table in a way that first 3 values should be 1st row, then the values from 4-6 need to be second row and so on.
Result should be:
12312,1231235,540945
4506,2349,2349
13409,3490834,1349
3490835,309034,2394
110,124349
Solved! Go to Solution.
Hi @Anonymous
Use List.Split. Place this code in a blank query to see the steps from your initial example:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc3BDcAwCAPAXfLuw2CnKrNE2X+NRoRI7e9kDIzRzGne5lViT3chtKmOO+FU/GFctdRK8FAn/Yb7ChGouTOqaKjHypX5Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", Int64.Type}}),
list_ = List.Split(#"Changed Type"[Col1],3),
#"Converted to Table" = Table.FromList(list_, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
#"Extracted Values"
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @Anonymous
Use List.Split. Place this code in a blank query to see the steps from your initial example:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc3BDcAwCAPAXfLuw2CnKrNE2X+NRoRI7e9kDIzRzGne5lViT3chtKmOO+FU/GFctdRK8FAn/Yb7ChGouTOqaKjHypX5Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", Int64.Type}}),
list_ = List.Split(#"Changed Type"[Col1],3),
#"Converted to Table" = Table.FromList(list_, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
#"Extracted Values"
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Great that actually works! At least with that I can do the necessary tranformations - thanks!
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.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |