CODEWITHSUNDEEP
powerbiodata
rick
rick

Reputation: 472

Power BI using $expand on columns

Power BI newbie here.

I wanted to build a Power BI visualization as shown in the sample report based on my Azure DevOps data (https://learn.microsoft.com/en-us/azure/devops/report/powerbi/sample-test-plans-execution-trend?view=azure-devops&tabs=powerbi).

Making the X-axis with DateSK seems not working as PowerBI treating it as a whole number and the way of X-axis in my visualization is taking it linearly (the plotted X-axis like this: 20200100 20200150 20200200 20200250 and so on). I wanted to have a view of day-by-day as shown in the Microsoft docs.

PowerBI also do not allow me to convert the DateSK data type from whole number into date format.

I have tried using $expand in my Power BI query but it seems the date column couldn't let me expand or I did it wrongly.

Please let me know if you have any workaround or ways to query using the $expand. I want to have a view of expanded Date format instead of DateSK.

Below is the reference from Microsoft docs as shown in the link above:

let 
    Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/TestPointHistorySnapshot?" 
        &"$apply=filter((TestSuite/TestPlanTitle eq '{testPlanTitle}') and (DateSK ge {startDate} and DateSK le {endDate}))" 
        &"/groupby(" 
            &"(DateSK)," 
            &"aggregate(" 
                &"$count as TotalCount," 
                &"cast(ResultOutcome  eq 'Passed', Edm.Int32) with sum as Passed," 
                &"cast(ResultOutcome  eq 'Failed', Edm.Int32) with sum as Failed," 
                &"cast(ResultOutcome eq 'Blocked', Edm.Int32) with sum as Blocked," 
                &"cast(ResultOutcome eq 'NotApplicable', Edm.Int32) with sum as NotApplicable," 
                &"cast(ResultOutcome eq 'None', Edm.Int32) with sum as NotExecuted," 
                &"cast(ResultOutcome ne 'None', Edm.Int32) with sum as Executed 
            ) 
        )", null, [Implementation="2.0"]) 
in 
    Source

Upvotes: 0

Views: 825

Answers (1)

RCGoforth
RCGoforth

Reputation: 146

I achieved this by converting the column twice, first to text, then to date.

Below I am showing the query changes from the advanced editor, but I made the column conversions in the visual query editor. I convert the counts to whole numbers, and the dateSK to text in the first step.

In the second step I Convert the Date column to a date, then rename the column to Date from DateSK.

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TotalCount", Int64.Type}, {"Passed", Int64.Type}, {"Failed", Int64.Type}, {"Blocked", Int64.Type}, {"NotApplicable", Int64.Type}, {"NotExecuted", Int64.Type}, {"Executed", Int64.Type}, {"DateSK", type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"DateSK", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"DateSK", "Date"}}),

Upvotes: 1

Related Questions