Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone
I have a data table obtained from a monitoring systems XML service.
I have transformed this up to this point
Location | Room1 |
Device IP Address | 10.1.1.1 |
Device Name | Name1 |
Reachability | PINGABLE |
Device IP Address | 10.1.1.2 |
Device Name | Name2 |
Reachability | PINGABLE |
Location | Room2 |
Device IP Address | 10.1.1.3 |
Device Name | Name3 |
Reachability | PINGABLE |
Location | Room3 |
Device IP Address | 10.1.1.4 |
Device Name | Name4 |
Reachability | PINGABLE |
Location | Room 4 |
Device IP Address | 10.1.1.5 |
Device Name | Name5 |
Reachability | DOWN |
Device IP Address | 10.1.1.6 |
Device Name | Name6 |
Reachability | DOWN |
Location | Room5 |
Device IP Address | 10.1.1.7 |
Device Name | Name7 |
Reachability | UP |
But i can not see a way to transorm it into something like this
Location | Device IP Address | Device Name | Reachability |
Room1 | 10.1.1.1 | Name1 | PINGABLE |
Room5 | 10.1.1.7 | Name7 | UP |
Any ideas please
Regards
Solved! Go to Solution.
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
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
Hello @Werick
found a really great solution for you. Check it out
let
Source = #table
(
{"Field","Value"},
{
{"Location","Room1"}, {"Device IP Address","10.1.1.1"}, {"Device Name","Name1"}, {"Reachability","PINGABLE"}, {"Device IP Address","10.1.1.2"}, {"Device Name","Name2"}, {"Reachability","PINGABLE"},
{"Location","Room2"}, {"Device IP Address","10.1.1.3"}, {"Device Name","Name3"}, {"Reachability","PINGABLE"}, {"Location","Room3"}, {"Device IP Address","10.1.1.4"},
{"Device Name","Name4"}, {"Reachability","PINGABLE"}, {"Location","Room 4"}, {"Device IP Address","10.1.1.5"}, {"Device Name","Name5"}, {"Reachability","DOWN"},
{"Device IP Address","10.1.1.6"}, {"Device Name","Name6"}, {"Reachability","DOWN"}, {"Location","Room5"}, {"Device IP Address","10.1.1.7"}, {"Device Name","Name7"},
{"Reachability","UP"}
}
),
GroupLocation = Table.Group(Source, {"Field"}, {{"AllRows", each _, type table [Location=text, Room1=text]}},GroupKind.Local, (group,current) => if Value.Compare(current[Field],"Location")<>0 then 0 else 1),
Transpose = Table.TransformColumns
(
GroupLocation,
{
{
"AllRows",
each Table.PromoteHeaders(Table.Transpose(_))
}
}
),
Combine = Table.Combine
(
Transpose[AllRows]
)
in
Combine
Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query, or I could create a custom function what makes it easier to apply if you are not used that much to power query.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi @Jimmy801
Thanks for the prompt reply.
This works, but as you guessed i am new to power query, so this does not make alot of sence to me yet.
My Code looks like this now
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
GroupLocation = Table.Group(Source, {"C1"}, {{"AllRows", each _, type table [Location=text, Room1=text]}},GroupKind.Local, (group,current) => if Value.Compare(current[C1],"Location")<>0 then 0 else 1),
Transpose = Table.TransformColumns
(
GroupLocation,
{
{
"AllRows",
each Table.PromoteHeaders(Table.Transpose(_))
}
}
),
Combine = Table.Combine
(
Transpose[AllRows]
)
in
Combine
The source is a table in Excel
C1 | C2 |
Location | Room1 |
Device IP Address | 10.1.1.1 |
Device Name | Name1 |
Reachability | PINGABLE |
Device IP Address | 10.1.1.2 |
Device Name | Name2 |
Reachability | PINGABLE |
Location | Room2 |
Device IP Address | 10.1.1.3 |
Device Name | Name3 |
Reachability | PINGABLE |
Location | Room3 |
Device IP Address | 10.1.1.4 |
Device Name | Name4 |
Reachability | PINGABLE |
Location | Room 4 |
Device IP Address | 10.1.1.5 |
Device Name | Name5 |
Reachability | DOWN |
Device IP Address | 10.1.1.6 |
Device Name | Name6 |
Reachability | DOWN |
Location | Room5 |
Device IP Address | 10.1.1.7 |
Device Name | Name7 |
Reachability | UP |
and it outputs this
Location | Device IP Address | Device Name | Reachability | Device IP Address_1 | Device Name_2 | Reachability_3 |
Room1 | 10.1.1.1 | Name1 | PINGABLE | 10.1.1.2 | Name2 | PINGABLE |
Room2 | 10.1.1.3 | Name3 | PINGABLE | |||
Room3 | 10.1.1.4 | Name4 | PINGABLE | |||
Room 4 | 10.1.1.5 | Name5 | DOWN | 10.1.1.6 | Name6 | DOWN |
Room5 | 10.1.1.7 | Name7 | UP |
I will figure it out eventually. But dont understand the extra cols?
Regards
Hello @Werick
i suppose that your data has to be grouped by Location... so whenever "Location" is stated this would be a new group. In some cases in between two locations the Device name and also other fields are stated twice. So how should they be treated?
Jimmy
Some of the "location" data does not exist so it is blank.
It would be better to work on the IP and the "Primary key" field
regards
Hello @Werick
this wasn't specified. The problem is .. how can you group them together? Is there any logic? Because to transform the data you need absolutly to group all rows, that belong together. If the IP would be always the first row of the data, then it would work.
By the way, where is the primary key field you are referencing?
Jimmy