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

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.

Helper I
Helper I

Missing column after transpose

Hello all, 

I have a data set in pivot table format with double headers. 


Total i have 14 markets. 

Up until Merge columns, I sill see 14 markets in total. 


However, after transpose, there are missing markets and attribute



Can anyone can explain me what i missing? 

Thank you 

Community Support
Community Support

Hi @betty_bui2602 


From the last screenshot, there are at least 9588 columns in the table after transposing. This is too large for a table. You can try adding the following steps to transform the table structure. Hope it will bring those missing data back. 


1. Promote first row as column headers. 

2. Replace "" to null in the first Market column.

3. Fill Down values in the first Market column. 

4. Select Market and Material columns at the same time, unpivot other columns. 


This will return a table in the following format. This is more friendly for further reporting in Power BI. 


    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcgxwAZJw5FhaXFKUmJOZiCzokZ+XruANJBCCsTrRSr6JJalFmYk5IG2OQMLJCUg4OwMJFxeixUAG+SeX5CelFinUKBgiW4sToeoxQpO2AGIzIDYGYiM8+ozJsMsEKARyowHUbBBtCrXPHGo3Lr2mZNhnhkudOYQC2W8C5sbGAgA=", 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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","",null,Replacer.ReplaceValue,{""}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{""}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filled Down", {"", "Material"}, "Attribute", "Value")
    #"Unpivoted Other Columns"


Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.