How to Move data ID from above the data to next to each line

[brochelle]

The way in which we get our data is dirty and requires lots of cleaning in order for us to use it. Most of the time the fix is just time consuming, but this particular issue is a little more complex. The data is given to us with multiple users ID, then the data below for that user; then the next user, their data, and so on. 

Example:

For me to properly ingest the data and put it into usable tables, I am currently inserting a row on the far left and manually copying and pasting the USER_ID and pasting it next to each row.

Example:

Is there an easier way to automate this in Power BI? 

[BA_Pete · Accepted solution]

Hi @brochelle ,

You should be able to do something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcqxDoAgDATQf+lMQml7UJ11cNU4EeL//4WgC8kNl7tXK93Xfj7HtlKgVJDdehlpoZIskVMUFhmvq7gi9aqFxwDAi31SeZIZhgz9pc+yvQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, transID = _t, exNumber1 = _t, locationID = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"date", type text}, {"transID", Int64.Type}, {"exNumber1", Int64.Type}, {"locationID", Int64.Type}}),
    addUserIDColumn = Table.AddColumn(chgTypes, "userID", each if [locationID] = null then [transID] else null),
    fillDownUserIDColumn = Table.FillDown(addUserIDColumn,{"userID"}),
    filterOutNullLocationID = Table.SelectRows(fillDownUserIDColumn, each ([locationID] <> null))
in
    filterOutNullLocationID

This gives me the following output:

Pete