Forum Discussion
Solved
Import XML and group same attributes into one row
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 🙂
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! 🙂
1 Reply
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! 🙂