Split column by row count

[Anonymous]

Hi,

I'm trying to use a XML provided by a web service, the service returns something like this example.

This is what I've managed to get with that data:

I can't find a way to split the data in an usefull way, is there any way to get the data split? It beeing splitting a column by row count or some other way that you find usefull?

Thanks in advance.

[az38 · Accepted solution]

Hi @Anonymous 

Produce the next workaround in the Power query

1. Create an Index column starting 1

2 add custom column

Custom = 
if Number.Mod([Index], 6) = 0 then "data_values_data_value" 
else if Number.Mod([Index], 6) = 1 then "data_values_date_time"
else if Number.Mod([Index], 6) = 2 then "data_values_data_type_id"
else if Number.Mod([Index], 6) = 3 then "data_values_height"
else if Number.Mod([Index], 6) = 4 then "data_values_lat_wgs84"
else if Number.Mod([Index], 6) = 5 then "data_values_lon_wgs84"
else "other"

3. add another custom column

ItemId = Number.RoundDown(([Index]-1)/6)+1

4. Remove Index column

5. pick Custom colum and execute Pivot Column with Do not aggregated function as advanced parameter

6. filter out row with ItemId=1

7. remove ItemId column

finally you will get smth like

let
    Source = Xml.Tables(File.Contents("C:\7eb5c62515428ce96d1ab37acd3e07b6.xml")),
    Table0 = Source{0}[Table],
    Table1 = Table0{0}[Table],
    Table2 = Table1{0}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table2,{{"iError", Int64.Type}}),
    #"Expanded getLatestData_Using_DataTypeIds_and_HoursResult" = Table.ExpandTableColumn(#"Changed Type", "getLatestData_Using_DataTypeIds_and_HoursResult", {"ArrayOfString"}, {"getLatestData_Using_DataTypeIds_and_HoursResult.ArrayOfString"}),
    #"Expanded getLatestData_Using_DataTypeIds_and_HoursResult.ArrayOfString" = Table.ExpandTableColumn(#"Expanded getLatestData_Using_DataTypeIds_and_HoursResult", "getLatestData_Using_DataTypeIds_and_HoursResult.ArrayOfString", {"string"}, {"getLatestData_Using_DataTypeIds_and_HoursResult.ArrayOfString.string"}),
    #"Expanded getLatestData_Using_DataTypeIds_and_HoursResult.ArrayOfString.string" = Table.ExpandTableColumn(#"Expanded getLatestData_Using_DataTypeIds_and_HoursResult.ArrayOfString", "getLatestData_Using_DataTypeIds_and_HoursResult.ArrayOfString.string", {"Element:Text"}, {"getLatestData_Using_DataTypeIds_and_HoursResult.ArrayOfString.string.Element:Tex"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded getLatestData_Using_DataTypeIds_and_HoursResult.ArrayOfString.string",{"sError", "iError"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if Number.Mod([Index], 6) = 0 then "data_values_data_value" 
else if Number.Mod([Index], 6) = 1 then "data_values_date_time"
else if Number.Mod([Index], 6) = 2 then "data_values_data_type_id"
else if Number.Mod([Index], 6) = 3 then "data_values_height"
else if Number.Mod([Index], 6) = 4 then "data_values_lat_wgs84"
else if Number.Mod([Index], 6) = 5 then "data_values_lon_wgs84"

else "other"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ItemId", each Number.RoundDown(([Index]-1)/6)+1),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Custom]), "Custom", "getLatestData_Using_DataTypeIds_and_HoursResult.ArrayOfString.string.Element:Tex"),
    #"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each ([ItemId] <> 1)),
    #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"ItemId"})
in
    #"Removed Columns2"

and

[Anonymous]

Thank you very much @az38 , it worked perfectly.

And thank you @Greg_Deckler for the tip.

[Greg_Deckler]

So generally you are going to do something like create an Index column. Then you use something like modulus to from a column that you can group on. I know @ImkeF has techniques for this, you can probably find something by looking on https://www.thebiccountant.com/