Import XML and group same attributes into one row

[Sleepw4lk3r]

Hello,

i'm very new to powerquery and try to import xml into excel. 

i want to add all RReal and UReal with the Same Startdate into the same Row.

i tried it with grouping but this ends in a nested table which i dont know how to get into one row.

so the raw data looks like this:

and want it to look like that:

i'm thankful for your help 🙂

[rohit_singh · Accepted solution]

Hi @Sleepw4lk3r ,

Please copy and paste the below M-code to see the steps in detail.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrDUMzDTMzIwMlIwsLAyBiJLJR00URNjK0OQqLGxuQEEADmGBoZ6hkqxOqQZYWgAB2CeqZ4R6WaYWaI6w4R0I8wNjJCMMAL5JBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"TEST-RESULTS.STARTDATE" = _t, #"TEST-RESULTS.ENDDATE" = _t, #"TEST-RESULTS.STEP_15.RREAL" = _t, #"TEST-RESULTS.STEP_15.UREAL" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TEST-RESULTS.STARTDATE", type datetime}, {"TEST-RESULTS.ENDDATE", type datetime}, {"TEST-RESULTS.STEP_15.RREAL", Int64.Type}, {"TEST-RESULTS.STEP_15.UREAL", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"TEST-RESULTS.STARTDATE", "TEST-RESULTS.ENDDATE"}, {{"Count", each _, type table [#"TEST-RESULTS.STARTDATE"=nullable datetime, #"TEST-RESULTS.ENDDATE"=nullable datetime, #"TEST-RESULTS.STEP_15.RREAL"=nullable number, #"TEST-RESULTS.STEP_15.UREAL"=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][#"TEST-RESULTS.STEP_15.RREAL"]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each [Count][#"TEST-RESULTS.STEP_15.UREAL"]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Count"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), " , "), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), " , "), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values1", "Custom", Splitter.SplitTextByDelimiter(" , ", QuoteStyle.Csv), {"Custom.1.1", "Custom.2", "Custom.3", "Custom.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}, {"Custom.4", Int64.Type}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Custom.1", Splitter.SplitTextByDelimiter(" , ", QuoteStyle.Csv), {"Custom.1.1.1", "Custom.1.2", "Custom.1.3", "Custom.1.4"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Custom.1.1.1", type number}, {"Custom.1.2", type number}, {"Custom.1.3", type number}, {"Custom.1.4", type number}})
in
    #"Changed Type2"

Input

Output

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂