Getting Data from Previous Rows

[Anonymous]

Hi All,

Bit of a challenge, not even sure where I can possibly start with this one. I have extracted a bunch (will eventually be in the thousands) of csv files and combined them. What I need to do is get the values in Collumn 4 and 5 highlighted in yellow into their own column. For instance rows 3-13 should read 400m in a new column and then Tier-3 Maiden in the next. Then from Rows 17-28 should read 390m in that first new column and Tier-3 Maiden in the second. I was thinking I could work with the index as well as that "~" value in column 1 to somehow get this to work. I know its possible in VBA (on a single sheet) but i am not sure how to do it in BI. 

Thanks in advance for any help

[Anonymous]

Sorry guys I should have closed this thread. I managed to use a different data source which was much easier to deal with. Thanks for your help though

[ziying35]

Hi, @Anonymous 

You can provide a file containing the source table with sensitive information removed, and a table of expected results. Upload it to the cloud and share the link here. That way the person offering you help can understand what results you need faster, and you can get a solution faster.

[Jimmy801]

Hello @Anonymous 

to give you here a good solution I need to know 2 logics

- how to identify the value to be written in a new column? (logic could be text that contains a "m").... from witch this function should work

- how to identify will witch row to write? Why row 13 and not 10 or 17?

Jimmy

[Anonymous]

Hi Jimmy

I was thinking you could pick up on the ~ in Column1, what ive done is move column 4 and 5 up by 1 index in a custom column. Therefore i can identify what row i need to pull from by the ~ in column 1. Then you'd write until you encountered the next ~ with some sort of if statement. 

Thanks

[Jimmy801]

Hello @Anonymous 

could you post a table here, that I can work with

Thanks

Jimmy