Missing column after transpose

[betty_bui2602]

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 

[v-jingzhang]

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. 

let
    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")
in
    #"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!