Forum Discussion

JAmbrose69's avatar
JAmbrose69
Frequent Visitor
3 years ago
Solved

Cleaning Data In PowerBi

I have trouble cleaning data in PowerBi. So I would like to know two things. The first is how do I clean the data example below. The second question, would there be any classes or any resources to help me clean data properly as the example is not as complex as some of the items in my queue. 

 

For the example below I have watched a video from MS PowerBi on "Clean data to include in a report" but it did not go deep enough to help me. I need to display the entity, measure, the actual data, all by date. Basically a trend over time for this specfic example. 

 

Example:

DateMeasureEntity1Entity2Entity3Entity4Average 
9/7/22measure1.99

.98

.98.95.975 
9/7/22measure211111 
9/7/22measure3.95.95.95.95.95 
9/14/22measure1.9.9.9.9.9 
9/14/22measure201.9.5.6 
9/14/22measure3.3.7.7.9.65 
9/21/22measure211111 
9/21/22measure3.9.9.9.9.9 

 

 

Please let me know if you have any additonal questions

 

Thanks in advance

Need Help
  • MFelix's avatar
    MFelix
    3 years ago

    Hi JAmbrose69 ,

     

    For the data that you present I believe that you need to unpivot your data. On Power Query select the columns Date and measure and then on Transform Select Unpivot Other columns:

     

    You will get a table with 4 columns:

    • Date
    • Measure
    • Attribute (entity names)
    • Value

    Rename the Attribute column and you should be all set:

    Now you can do a filter on measure or create the value based on entity:

     

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    There is a basic rule - Power BI wants narrow tables.  Your sample data is a prime example of what not to do - using wide tables.  So the most frequent cleaning technique is "unpivoting"

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ31zcyUtJRyk1NLC4tSjUEMvUsLcGkBRJpqhSrg6kaxDREwdhUGUNNwCQhqg1NsDgBncCqFMQ0gNoNUYjDTLATwIQ5nICZaWRIlJ/QlBljd2UsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Measure = _t, Entity1 = _t, Entity2 = _t, Entity3 = _t, Entity4 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Date", "Measure"}, "Entity", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Date", type date}, {"Value", type number}})
in
    #"Changed Type"

    Note that I also dropped the Average column - that can be computed in Power BI.

     

  • MFelix's avatar
    MFelix
    Super User
    3 years ago

    Hi JAmbrose69 ,

     

    For the data that you present I believe that you need to unpivot your data. On Power Query select the columns Date and measure and then on Transform Select Unpivot Other columns:

     

    You will get a table with 4 columns:

    • Date
    • Measure
    • Attribute (entity names)
    • Value

    Rename the Attribute column and you should be all set:

    Now you can do a filter on measure or create the value based on entity: