Missing column after transpose

Hi Anonymous 

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!