topic Re: Power Query: Group Value In Period. in Power Query

Power Query: Group Value In Period.

trungduc_acc — Mon, 05 Jul 2021 13:57:21 GMT

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

Re: Power Query: Group Value In Period.

smpa01 — Mon, 05 Jul 2021 14:16:32 GMT

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.

Re: Power Query: Group Value In Period.

trungduc_acc — Mon, 05 Jul 2021 14:52:40 GMT

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

Re: Power Query: Group Value In Period.

smpa01 — Mon, 05 Jul 2021 19:36:00 GMT

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"

Re: Power Query: Group Value In Period.

trungduc_acc — Mon, 05 Jul 2021 22:47:08 GMT

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:

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