Forum Discussion
Solved
Power Query: Group Value In Period.
Hi all! I have Query 1 and Query 2 as below: I want group text in #"Query 1"[Value] Column by period in #"Query 2". Result as below: Please, help me! Thank you very much. Link Data F...
You would need a calendar table for this
/*_fact1*/ let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1ACElHSjHSNfQVClWJxrBNUOSM7JQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [from = _t, to = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"from", type date}, {"to", type date}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "from", "to"}), #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each {Number.From([from])..Number.From([to])}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Custom"}, _fact2, {"dates"}, "_fact2", JoinKind.LeftOuter), #"Expanded _fact2" = Table.ExpandTableColumn(#"Merged Queries", "_fact2", {"value"}, {"value"}), #"Filtered Rows" = Table.SelectRows(#"Expanded _fact2", each ([value] <> null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"Index", "from", "to"}, {{"ad", each _, type table [Index=number, from=nullable date, to=nullable date, value=nullable text]}}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each let x =[ad], #"Removed Columns1" = Table.RemoveColumns(x,{"Index", "from", "to"}), value = #"Removed Columns1"[value] in value), #"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"ad"}), #"Extracted Values" = Table.TransformColumns(#"Removed Columns2", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}) in #"Extracted Values"/*_fact2*/ let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1ACElHSVHpVgdJCFjoJATqpAJUMgZScgIiIBCLihCRhZAIVdUITOgkJtSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [dates = _t, value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"dates", type date}, {"value", type text}}) in #"Changed Type"/*calendar*/ let Source = {44197..44562}, #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.From([Column1])) in #"Added Custom"
You would need a calendar table for this
/*_fact1*/
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1ACElHSjHSNfQVClWJxrBNUOSM7JQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [from = _t, to = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"from", type date}, {"to", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "from", "to"}),
#"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each {Number.From([from])..Number.From([to])}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Custom"}, _fact2, {"dates"}, "_fact2", JoinKind.LeftOuter),
#"Expanded _fact2" = Table.ExpandTableColumn(#"Merged Queries", "_fact2", {"value"}, {"value"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded _fact2", each ([value] <> null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Index", "from", "to"}, {{"ad", each _, type table [Index=number, from=nullable date, to=nullable date, value=nullable text]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each let x =[ad],
#"Removed Columns1" = Table.RemoveColumns(x,{"Index", "from", "to"}),
value = #"Removed Columns1"[value]
in
value),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"ad"}),
#"Extracted Values" = Table.TransformColumns(#"Removed Columns2", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
#"Extracted Values"
/*_fact2*/
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1ACElHSVHpVgdJCFjoJATqpAJUMgZScgIiIBCLihCRhZAIVdUITOgkJtSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [dates = _t, value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"dates", type date}, {"value", type text}})
in
#"Changed Type"/*calendar*/
let
Source = {44197..44562},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.From([Column1]))
in
#"Added Custom"
Thank you very much @smpa01
Your solution is perfect. That's really what I need. Allow me to modify it a bit. Delete add index step and all step after GroupBy step in /*_fact1*/ as bellow:
/*_fact1*/
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1ACElHSjHSNfQVClWJxrBNUOSM7JQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [from = _t, to = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"from", type date}, {"to", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From([from])..Number.From([to])}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Custom"}, _fact2, {"dates"}, "_fact2", JoinKind.LeftOuter),
#"Expanded _fact2" = Table.ExpandTableColumn(#"Merged Queries", "_fact2", {"value"}, {"value"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded _fact2", each ([value] <> null)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"from", "to"}, {{"Value", each Text.Combine([value],","), type text}})
in
#"Grouped Rows"
Number of step less than but is it's performance better than? I don't know.
Query /*calendar*/ in your solution. what does it use? I don't know, too. I only use /*_fact1*/ and /*_fact2*/.
Thanks again smpa01