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.
Good morning guys,
I have a table which shown in below and need to convert it in well manner.
Anyone have idea how may i do it.
Thank you guys Cheers
Oli
Solved! Go to Solution.
Hi @Oli2021 ,
provided a stable structure like in your sample, you can do it like so:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KSS1KTbFS0lEyNNA3MNY3MjAyBHJAKFYnWsk5JzM1rwSsyi8fpErZyNTE3MwCyAopSswrhgiamhsYWJqDNZBpoCUWAy2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"All", each Table.FromRows( {List.Select( List.Union( Table.ToColumns( Table.SelectColumns(_,{"Column2", "Column4"}) ) ), each _ <> null)})}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"})
in
#"Expanded All"
Next time, please provide sample data in a usable form, so that we don't have to type that in manually:
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @Oli2021 ,
provided a stable structure like in your sample, you can do it like so:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KSS1KTbFS0lEyNNA3MNY3MjAyBHJAKFYnWsk5JzM1rwSsyi8fpErZyNTE3MwCyAopSswrhgiamhsYWJqDNZBpoCUWAy2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"All", each Table.FromRows( {List.Select( List.Union( Table.ToColumns( Table.SelectColumns(_,{"Column2", "Column4"}) ) ), each _ <> null)})}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"})
in
#"Expanded All"
Next time, please provide sample data in a usable form, so that we don't have to type that in manually:
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@Oli2021 , I do no think, this is an ideal format of data for any BI tool. Better to correct the source.
That will be a lot of effort to get this rolling.
@ImkeF , any solution for this ?
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |