Cleaning Data In PowerBi

[JAmbrose69]

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:

Date	Measure	Entity1	Entity2	Entity3	Entity4	Average
9/7/22	measure1	.99	.98	.98	.95	.975
9/7/22	measure2	1	1	1	1	1
9/7/22	measure3	.95	.95	.95	.95	.95
9/14/22	measure1	.9	.9	.9	.9	.9
9/14/22	measure2	0	1	.9	.5	.6
9/14/22	measure3	.3	.7	.7	.9	.65
9/21/22	measure2	1	1	1	1	1
9/21/22	measure3	.9	.9	.9	.9	.9

Please let me know if you have any additonal questions

Thanks in advance

[lbendlin]

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 · Accepted solution]

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: