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

[francobonelli]

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.

[Icey · Accepted solution]

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:

Best Regards,

Icey

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

[francobonelli]

Thank you for your help again Amit :). 

I have found the solution I needed :).

Best regards

[Icey · Accepted solution]

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:

Best Regards,

Icey

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

[francobonelli · Accepted solution]

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]

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

[francobonelli]

Hi Amit

Thanks for your reply.

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

The formula is giving an error:

Regards

Franco the newbie 😄

[amitchandak]

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

[francobonelli]

Hi Amit

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

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

Regards

[francobonelli]

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