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      Location Room1 Device IP Address 10.1.1.1 Devi...
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

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

     

    • Werick's avatar
      Werick
      Frequent Visitor
      6 years ago

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

        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