Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Split column by row count

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.

Need Help
  • az38's avatar
    az38
    6 years ago

    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

     

     

3 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you very much az38 , it worked perfectly.

       

      And thank you Greg_Deckler for the tip.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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/