Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to 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! 🙂
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! 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.