cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bcharlto Frequent Visitor
Frequent Visitor

Turning table visual into data table that I can join to

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...

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: Turning table visual into data table that I can join to

@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 )
)

357.jpg

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,

2 REPLIES 2
Moderator v-sihou-msft
Moderator

Re: Turning table visual into data table that I can join to

@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 )
)

357.jpg

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 Frequent Visitor
Frequent Visitor

Re: Turning table visual into data table that I can join to

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

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 261 members 3,498 guests
Please welcome our newest community members: