Forum Discussion

bcharlto's avatar
bcharlto
Frequent Visitor
10 years ago
Solved

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

  • v-sihou-msft's avatar
    v-sihou-msft
    10 years ago

    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,

2 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    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's avatar
      bcharlto
      Frequent Visitor
      10 years ago

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