Power Query: Group Value In Period.

[trungduc_acc]

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 File: https://1drv.ms/x/s!AjR7DINn-uTQgpsC-UicRFc1kEUxWA?e=OsT31r

[smpa01]

Are you getting it from a SQL server? It is much easier to this on a Sql-Server side. Please provide some insight on the data source you are querying it from. If it can be done natively, you don't need to be bothered about power query doing this job for you.

[trungduc_acc]

I query from xls files (or table), not SQL server use PowerQuery in Excel, not Power BI

[smpa01 · Accepted solution]

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"

[trungduc_acc]

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