Recreate an Excel table in Power BI

Anonymous — Wed, 22 May 2024 20:05:47 GMT

Hello community.

Please help me recreate the Excel table in Power BI.

Expected result:

Link to sample file.

Thanks in advance.

Re: Recreate an Excel table in Power BI

aduguid — Thu, 23 May 2024 01:13:21 GMT

First I would create a calculated column in "fct_test_results".

Rank = RANKX( FILTER( fct_test_results, fct_test_results[Course] = EARLIER(fct_test_results[Course]) ) , fct_test_results[Pace], , ASC )

Then you can create a DAX query for the results.

fct_test_results_pivot = VAR _result = UNION( SELECTCOLUMNS(fct_test_results , "Course" , fct_test_results[Course] , "Rank" , fct_test_results[Rank] , "Column Value" , fct_test_results[Pace] , "Column Name" , "Pace") , SELECTCOLUMNS(fct_test_results , "Course" , fct_test_results[Course] , "Rank" , fct_test_results[Rank] , "Column Value" , FORMAT(fct_test_results[Pace Test Result], "0%") , "Column Name" , "Result") , SELECTCOLUMNS(fct_test_results , "Course" , fct_test_results[Course] , "Rank" , fct_test_results[Rank] , "Column Value" , FORMAT(fct_test_results[Pace Test Date], "MM/dd/yyyy") , "Column Name" , "Date") ) RETURN _result

If you want a different sort, you can create an additional DAX query and then relate it to the pivot.

Metric List = VAR _results = DATATABLE ( "Column Name", STRING , "Column Order", INTEGER , { {"Pace", 1} , {"Result", 2} , {"Date", 3} } ) RETURN _results

In this matrix I've used the [Course] from the "fct_test_results_pivot", [Column Name] from "Metric List" (which has a column sort on [Column Order]), [Rank] from "fct_test_results_pivot" and the first [Column Value] from "fct_test_results_pivot". If you have a unique key/id, you can include it in the pivot and relate it back to your original table so date filters would still work.

Re: Recreate an Excel table in Power BI

Anonymous — Thu, 23 May 2024 03:32:58 GMT

aduguid thank you!

Re: Recreate an Excel table in Power BI

aduguid — Thu, 23 May 2024 03:35:29 GMT

no worries, anytime, happy to help

topic Re: Recreate an Excel table in Power BI in DAX Commands and Tips

Recreate an Excel table in Power BI

Re: Recreate an Excel table in Power BI

Re: Recreate an Excel table in Power BI

Re: Recreate an Excel table in Power BI