Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

Power Query - Append Data

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?

 

SteffTh1994_0-1649070684658.png

 

Thank you!

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

Hi @Anonymous ,

 

  1. Load this file and use the headers as first row.
  2. Transpose it
  3. Format the column1 which contains "name","age" and "size".vchenwuzmsft_2-1649315333254.png

     

  4. Add index and pivot the column2 which contains values of name, age, and size.vchenwuzmsft_3-1649315376949.png

     

  5. Fill up Age and Size.
  6. Remove index and filter out the name where is null.

Result:

vchenwuzmsft_4-1649315481398.png

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.

View solution in original post

2 REPLIES 2
v-chenwuz-msft
Community Support
Community Support

Hi @Anonymous ,

 

  1. Load this file and use the headers as first row.
  2. Transpose it
  3. Format the column1 which contains "name","age" and "size".vchenwuzmsft_2-1649315333254.png

     

  4. Add index and pivot the column2 which contains values of name, age, and size.vchenwuzmsft_3-1649315376949.png

     

  5. Fill up Age and Size.
  6. Remove index and filter out the name where is null.

Result:

vchenwuzmsft_4-1649315481398.png

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.

amitchandak
Super User
Super User

@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.

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.