Turning table visual into data table that I can join to

[bcharlto]

I have created a table visual that has columns from my original query, but also includes a measure that I want to be able to join back to my original query. Can I create a Data Table from a Table Visual?

Table Visual:

Item: From Query1

Store: From Query1

Week_Ending: From Query1

AvgPrice: sum(Query1[$Sales] )/sum(Query1[UnitSales]

RegularPrice = calculate(MAX(Query1[AvgPriceCol]),datesinperiod(Query1[Week_Ending],lastdate(Query1[Week_Ending]),-84,DAY))

*AvgPriceCol is a column from Query1 and is the same result as AvgPrice

I want to join RegularPrice back to Query1 and have the join based on Item, Store, and Week_Ending, however it's just a table visual right now...

[v-sihou-msft · Accepted solution]

@bcharlto

If you want to make RegularPrice to be a calculated column in Query1 table, you can use following formula.

RegularPrice_Column = 
CALCULATE (
    MAX ( Query1[AvgPriceCol] ),
    FILTER (
        Query1,
        Query1[Item] = EARLIER ( Query1[Item] )
            && Query1[Store] = EARLIER ( Query1[Store] )
    ),
    DATESINPERIOD ( Query1[Week_Ending], LASTDATE ( Query1[Week_Ending] ), -84, DAY )
)

You can use R scripts to export data from power bi back to SQL, please refer to links below:

http://www.thebiccountant.com/2015/12/28/how-to-export-data-from-power-bi-and-power-query/ http://stackoverflow.com/questions/19190744/how-to-quickly-export-data-from-r-to-sql-server

*Note: R visuals in Power BI Desktop has a few limitations.

1. Data size limitations – data used by the R visual for plotting is limited to 150,000 rows. If more than 150,000 rows are selected, only the top 150,000 rows are used and a message is displayed on the image.
2. Calculation time limitation – if an R visual calculation exceeds 5 minutes the execution times out, resulting in an error.

If you want to export visual report data to .csv file. You can select the ellipses in the top right corner of the visualization and choose the Export data icon.

*Note: 30,000 rows is the limitation for exporting visual report data to .csv file.

Regards,

[bcharlto]

Wow! This is amazing!! Thank you! I have 14 million rows. Works like a charm!