Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
Hello all,
I currently have the following problem with a data set. The data is delivered in "data blocks" and not in a database notation. Now I am trying to transform them with the help of the query editor so that I can work with them easily.
Can anyone of you maybe help me? With which function do I get my data transformed as desired?
Thank you!
Solved! Go to Solution.
Hi @Anonymous ,
Result:
Code you can refer:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtSS1S0lEyNAUR5hZAEogCEktzQHwLMGEAEfRKzcurBNJGxiBRM1Ol2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Age = _t, Size = _t, #"(blank)" = _t, Name.1 = _t, Age.1 = _t, Size.1 = _t, #"(blank).1" = _t, Name.2 = _t, Age.2 = _t, Size.2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Age", Int64.Type}, {"Size", Int64.Type}, {"(blank)", type text}, {"Name.1", type text}, {"Age.1", Int64.Type}, {"Size.1", Int64.Type}, {"(blank).1", type text}, {"Name.2", type text}, {"Age.2", Int64.Type}, {"Size.2", Int64.Type}}),
#"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
#"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type text}, {"Column9", type text}, {"Column10", type any}, {"Column11", type any}}),
#"Transposed Table" = Table.Transpose(#"Changed Type1"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Column1.2"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
#"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Column1.1]), "Column1.1", "Column2"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"(blank)"}),
#"Filled Up" = Table.FillUp(#"Removed Columns1",{"Age", "Size"}),
#"Removed Columns2" = Table.RemoveColumns(#"Filled Up",{"Index"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns2", each [Name] <> null and [Name] <> "")
in
#"Filtered Rows"
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Result:
Code you can refer:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtSS1S0lEyNAUR5hZAEogCEktzQHwLMGEAEfRKzcurBNJGxiBRM1Ol2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Age = _t, Size = _t, #"(blank)" = _t, Name.1 = _t, Age.1 = _t, Size.1 = _t, #"(blank).1" = _t, Name.2 = _t, Age.2 = _t, Size.2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Age", Int64.Type}, {"Size", Int64.Type}, {"(blank)", type text}, {"Name.1", type text}, {"Age.1", Int64.Type}, {"Size.1", Int64.Type}, {"(blank).1", type text}, {"Name.2", type text}, {"Age.2", Int64.Type}, {"Size.2", Int64.Type}}),
#"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
#"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type text}, {"Column9", type text}, {"Column10", type any}, {"Column11", type any}}),
#"Transposed Table" = Table.Transpose(#"Changed Type1"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Column1.2"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
#"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Column1.1]), "Column1.1", "Column2"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"(blank)"}),
#"Filled Up" = Table.FillUp(#"Removed Columns1",{"Age", "Size"}),
#"Removed Columns2" = Table.RemoveColumns(#"Filled Up",{"Index"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns2", each [Name] <> null and [Name] <> "")
in
#"Filtered Rows"
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Unpivot, and then pivot should do it
https://radacad.com/pivot-and-unpivot-with-power-bi
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.