Forum Discussion

Werick's avatar
Werick
Frequent Visitor
6 years ago
Solved

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

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

6 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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

    • Werick's avatar
      Werick
      Frequent Visitor
      6 years ago

      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's avatar
        Jimmy801
        Community Champion
        6 years ago

        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

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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