Forum Discussion

TAN_THOMAS's avatar
TAN_THOMAS
Frequent Visitor
2 years ago
Solved

Pivot column leads to Null value

I used pivot column (Transform > Pivot Column) to make each row header and column header to be values that I want. However, power BI automatically fills in the null values for rows that the value dont tally.

 

I cannot use replace null values directly because the replacement will lead to inputing a value, leading to data inaccuracy. 

I have also tried Fill down and Fill Up for Column1 and Column2 but it fills the data with value from the next row which also leads to data inaccuracy. 

 

It will lead to my table like this which is not what I want. 

 

I want the data to be like that: 

 

Need Help
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    2 years ago

    TAN_THOMAS , The second table does not seem like a pivoted table. We should have got some headers. 

    I changed the data type as text and able to pivot

     

     



    Power query code, use in blank query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWJVgIyksAMIyArGcwyBrJSwCwTICsVpgxMm0I0xgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Column1]), "Column1", "Column2", List.Max)
in
    #"Pivoted Column"

     

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    TAN_THOMAS , The second table does not seem like a pivoted table. We should have got some headers. 

    I changed the data type as text and able to pivot

     

     



    Power query code, use in blank query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWJVgIyksAMIyArGcwyBrJSwCwTICsVpgxMm0I0xgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Column1]), "Column1", "Column2", List.Max)
in
    #"Pivoted Column"