Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Werick
Frequent Visitor

help with transforming data

Hello everyone

 

I have a data table obtained from a monitoring systems XML service.  

 

I have transformed this up to this point 

 

 

LocationRoom1
Device IP Address10.1.1.1
Device NameName1
ReachabilityPINGABLE
Device IP Address10.1.1.2
Device NameName2
ReachabilityPINGABLE
LocationRoom2
Device IP Address10.1.1.3
Device NameName3
ReachabilityPINGABLE
LocationRoom3
Device IP Address10.1.1.4
Device NameName4
ReachabilityPINGABLE
LocationRoom 4
Device IP Address10.1.1.5
Device NameName5
ReachabilityDOWN
Device IP Address10.1.1.6
Device NameName6
ReachabilityDOWN
LocationRoom5
Device IP Address10.1.1.7
Device NameName7
ReachabilityUP

 

But i can not see a way to transorm it into something like this

 

LocationDevice IP AddressDevice NameReachability
Room110.1.1.1Name1PINGABLE
Room510.1.1.7Name7UP

 

 

Any ideas please

 

Regards

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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

Jimmy801
Community Champion
Community Champion

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

 

C1C2
LocationRoom1
Device IP Address10.1.1.1
Device NameName1
ReachabilityPINGABLE
Device IP Address10.1.1.2
Device NameName2
ReachabilityPINGABLE
LocationRoom2
Device IP Address10.1.1.3
Device NameName3
ReachabilityPINGABLE
LocationRoom3
Device IP Address10.1.1.4
Device NameName4
ReachabilityPINGABLE
LocationRoom 4
Device IP Address10.1.1.5
Device NameName5
ReachabilityDOWN
Device IP Address10.1.1.6
Device NameName6
ReachabilityDOWN
LocationRoom5
Device IP Address10.1.1.7
Device NameName7
ReachabilityUP

 

 

 

and it outputs this

 

LocationDevice IP AddressDevice NameReachabilityDevice IP Address_1Device Name_2Reachability_3
Room110.1.1.1Name1PINGABLE10.1.1.2Name2PINGABLE
Room210.1.1.3Name3PINGABLE   
Room310.1.1.4Name4PINGABLE   
Room 410.1.1.5Name5DOWN10.1.1.6Name6DOWN
Room510.1.1.7Name7UP   


I will figure it out eventually. But dont understand the extra cols?

 

Regards

 

Jimmy801
Community Champion
Community Champion

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?

grafik.png

 

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

 

Jimmy801
Community Champion
Community Champion

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors