help with transforming data

Hi Werick ,

I think the below gets you close to what you want - it does not list all IPs/Devices for each room as columns, rather it follows the database format - one row for each device, rather than each room, which makes it easier to deal with, but you can transform it further to bring to the desired view/layout.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8slPTizJzM9T0lEKys/PNVSK1YlWckkty0xOVfAMUHBMSSlKLS4Gyhoa6BmCILICv8TcVKAUiIKIB6UmJmckJmXmZJZUAiUCPP3cHZ18XAkZaoTDUCOChqI534iQTcY4bDIm1SZjQjaZ4LDJhFSbFEwIWWWKwypTbFa5+If7ETLQDIeBZngNRHO3KSFbzHHYYo7NltAApdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Attribute = _t, Value = _t]),
    mTable= Source,
    mLocationColumn = "Attribute",
    mValueColumn = "Value",

    Transform = List.Accumulate(Table.ToRecords(mTable), {{}, []}, (s, a)=>
                if Record.Field(a, mLocationColumn) <> "Reachability" then {s{0}, Record.Combine({s{1}, Record.FromList ({Record.Field(a, mValueColumn)}, {Record.Field(a, mLocationColumn)})})} else {s{0} & {Record.Combine({s{1}, Record.FromList ({Record.Field(a, mValueColumn)}, {Record.Field(a, mLocationColumn)})})}, []}),
    MakeTable = Table.FromRecords(Transform{0}, null, MissingField.UseNull),
    #"Filled Down" = Table.FillDown(MakeTable,{"Location"})
in
    #"Filled Down"

This code assumes that every data block ends with "Reachability" and uses it as a marker. The main magic happens in the Transform step. It goes through the table line by line, builds a record for each data block and then appends it to the list of records. Then this list is transformed to table in the MakeTable.

Saying that, as far as I understand, your input data come in am XLM format. I have a strange impression that it may be transformed straight into the target table format, by using XML import function in PBI. If this is possible, it would be a better option for large datasets. If we are talking about a few hundred lines, it probably does not make a huge difference, though.

Kind regards,

JB