CODEWITHSUNDEEP
powerbipowerquery
Atmira
Atmira

Reputation: 249

Extract data to column based on previous cell value in PowerBI

Short version: In PowerBI, M-language I need to extract the addresses from the column "Data" to column "Address" based on the knowledge that the address is always in the following cell of a value in the "ID" column.

Description: I have the Columns "Data" and "ID" in my dataset. I need to get the address from the column "Data" that contains a lot of random information. I know that the address is always placed in the following cell of a number in the "ID" Column. I want to extract the addresses, so they are placed next to the ID as shown in the column "Address". The amount of rows between each ID varies, and therefore I cannot do something like "extract each 6th row" or so by using index/rank etc. The data is basically structured as shown.

Tried Methods: I know I can extract this with some basic Excel-formula typing, but as this step is only one part of a long query, I would like to to this in PowerQuery/M. I've tried several solutions such as making index-columns and duplicating datasets and removing first row followed by merging the two tables and the method described here: https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/.

But both seems very complex to quite a simple problem, so hopefully someone has a better solution.

Data            ID      Address
RandomData      1       Address_Here
Address         null    
RandomData      null    
RandomData      2       Address_Here
Address         null    
RandomData      null    
RandomData      null    
RandomData      3       Address_Here
Address         null    
RandomData      null    
RandomData      null    
RandomData      null    
RandomData      4       Address_Here
Address         null    
RandomData      null    
RandomData      5       Address_Here
Address         null    
RandomData      null    
RandomData      null

Upvotes: 1

Views: 436

Answers (2)

horseyride
horseyride

Reputation: 21413

Add column .. .Index column ...

Add column .. custom column ... using formula

=try if #"Added Index"{[Index]+1}[ID]=null and [ID]<> null then #"Added Index"{[Index]+1}[Data] else null otherwise null

Upvotes: 2

W.B.
W.B.

Reputation: 5525

One approach that comes to mind. Add two index column - one starting from 0, the other one starting from 1, then join (merge) tables on these columns.

AddIndex0 = Table.AddIndexColumn(SourceTable, "Index0", 0, 1, Int64.Type),
AddIndex1 = Table.AddIndexColumn(AddIndex0, "Index1", 1, 1, Int64.Type),
Merged = Table.ExpandTableColumn(
    Table.NestedJoin(AddIndex0, "Index0", AddIndex1, "Index1", "Joined", JoinKind.LeftOuter),
    "Joined",
    {"Data", "ID"},
    {"Prev_Data", "Prev_ID"}
)

Upvotes: 1

Related Questions