Skip to main content
cancel
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.

Reply
betty_bui2602
Helper I
Helper I

Missing column after transpose

Hello all, 

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

betty_bui2602_0-1671019844778.png

Total i have 14 markets. 

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

betty_bui2602_1-1671019965416.png

However, after transpose, there are missing markets and attribute

betty_bui2602_2-1671020099365.png

betty_bui2602_3-1671020133176.png

Can anyone can explain me what i missing? 

Thank you 

1 REPLY 1
v-jingzhang
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. 

vjingzhang_1-1671087281359.png

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!

Helpful resources

Announcements
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

PBI_APRIL_CAROUSEL1

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.