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

Help Power query!!! New column with specific referenced cells

Hi everyone,

 

I have tried this multiple ways/times and am now getting a headache form banging my head against the wall 🙂 .

 

My issue is that I have a column taken from my source with multiple information and data types.

The report updates everyday with the latest data and I am only interested in the total parts produced cells in this column for my visual and these values are in the same cell locations each day.

 

I have done it with a simple filter on the column for the first day, but the next day shows a blank report because the filter is made to show the specific values form the previous day only!

 

Is it possible create a new column and return only these 5 cells that I require? Is it possible to make a new column and reference the the cells {12,24,48,60,72} only so that the values update daily with the refreshed data?

 

Any suggestions would be greatly appreciated!!!

 

Regards 

 

Franco.

2 ACCEPTED SOLUTIONS

Hi @francobonelli ,

 

Glad to hear that you have found the solution. Could you share us your solution?

 

In addition, you can add an index column to get what you want like so:

filter table.gif

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Hi Icey

 

Yes, the solution started by me remebering that Power BI works in tables etc and not columns & rows like excel.

So, 1st I set an index and filtered the data I wanted out relative to the 1st index rows[12,24,36,48,60]. Then I added a new index column to start from 1 on this filtered data and used this 'new' index to link the table to my other tables.

 

Thanks for all your help everyone and interest 🙂

 

Regards

 

Franco.

 

 

View solution in original post

8 REPLIES 8
francobonelli
Frequent Visitor

Thank you for your help again Amit :). 

I have found the solution I needed :).

 

Best regards

Hi @francobonelli ,

 

Glad to hear that you have found the solution. Could you share us your solution?

 

In addition, you can add an index column to get what you want like so:

filter table.gif

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi Icey

 

Yes, the solution started by me remebering that Power BI works in tables etc and not columns & rows like excel.

So, 1st I set an index and filtered the data I wanted out relative to the 1st index rows[12,24,36,48,60]. Then I added a new index column to start from 1 on this filtered data and used this 'new' index to link the table to my other tables.

 

Thanks for all your help everyone and interest 🙂

 

Regards

 

Franco.

 

 

amitchandak
Super User
Super User

@francobonelli , do you have cell number column. Else add an index column, if that gives the cell/row number you want you can have new column or measure https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

 

new column =

if( [index] in {12,24,48,60,72}  , [Col1], blank())

 

 



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!

Hi Amit

 

Thanks for your reply.

 

I have a number column and thus the cell/row locations I would like to show.

francobonelli_0-1619423249939.png

 

The formula is giving an error:

francobonelli_1-1619423852383.png

 

Regards

Franco the newbie 😄

@francobonelli , Sorry that was dax code, in power query try like

 

 

= if  [index] = 12 or   [index] = 24 or   [index] = 48 or   [index] = 60 or   [index] = 72 then [Col1]

 

or

new column =

if  [index] in {12,24,48,60,72} then   [Col1]



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!

Hi Amit

 

I had to change text to type whole number to avoid errors, but the new column just shows blank.

 

francobonelli_0-1619428707419.png

 

I just need to show the values at those specific cells(in the defined range) in a new column.

 

Regards

 

Hi Amit again! 🙂

I guess what I am trying to do is find the Power BI equivalent of the =index formula in MS Excel, which until now, I don't think there is a simple formula for as far as I can find.

 

Regards

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors