Forum Discussion

TimRattray's avatar
TimRattray
New Member
7 years ago
Solved

Editing the content of the data source

We have a spreadsheet that is exported from a third party regularly.  The spreadsheet has too much detail in the field we want to report on in Power BI.  For example...

  • iPad Air 2 (64 GB Silver)
  • iPad Air 2 (64 GB Space Grey)
  • iPad Air 2
  • iPad Air 2 (256 GB Silver)

I don't care about the colour! (or the RAM.)   How do I edit, in Power BI, that field?  This is easy in Excel:- LEFT(A2, FIND("(",A2)-1) results in "iPad Air 2" for all of them, which allows us to count them.

  • iPad Air 2

 

How do I do this in Power BI?  I don't want to edit the spreadsheet each time it is created.

Thanks

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi TimRattray ,

    It seems that you want to edit your data source before create the report in Power BI.

    Normally, we could shape and clean the data source in Query Editor.

    If I understand your requirement correctly that you want to shape the data below.

    • iPad Air 2 (64 GB Silver)
    • iPad Air 2 (64 GB Space Grey)
    • iPad Air 2
    • iPad Air 2 (256 GB Silver)

    If you want to only retrieve the data iPad Air 2, you could use the feature Split column in Query Editor.

    If you still need help, please share your data sample and your desired output so that we could help further on it.

    Best  Regards,

    Cherry

     

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Hi TimRattray ,

    It seems that you want to edit your data source before create the report in Power BI.

    Normally, we could shape and clean the data source in Query Editor.

    If I understand your requirement correctly that you want to shape the data below.

    • iPad Air 2 (64 GB Silver)
    • iPad Air 2 (64 GB Space Grey)
    • iPad Air 2
    • iPad Air 2 (256 GB Silver)

    If you want to only retrieve the data iPad Air 2, you could use the feature Split column in Query Editor.

    If you still need help, please share your data sample and your desired output so that we could help further on it.

    Best  Regards,

    Cherry

     

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Hi TimRattray ,

    Have you solved your problem?

    If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

    If you still need help, please share some data sample which could reproduce your scenario and your desired output so that we could help further on it.

    Best  Regards,

    Cherry