Forum Discussion
Solved
Grouping and summarizing into rows with column data
Have set of Site information that outputs data into various rows. Need to summarize all those invidiual SiteNo's with data in their own columns. Have been working at this for a while before request...
You should be able to do this all from the UI:
- Remove the unneeded columns "Code" and "Oid
- Pivot the "Description" Column
- Values Column = Text
- Advanced Options => Don't Aggregate
- Then reorder your columns as you wish
try this
let
Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lc/BEoIgEAbgV3E42yBQFkdUbCgDB1Y6OL7/a7RkTDPapcvCLnz/wDwTJiQpCeOyxqUzca0mGGdxe1PtPTgbzTBobIN6jIM+KLKUSQocCc7/h4kw+ctZ/Rydhy9pPqTOJIBKxw0mG3stUjsFHKgWTNR7eM7Q9f1ai9ZZ8Hi/6BQkUR0pO9H3Rzb4krEHn57nNUze7gMkrdg2YHkB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Oid = _t, SiteNo = _t, Code = _t, Description = _t, Text = _t, SBSite.CustomerNo = _t]),
#"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Oid", Int64.Type}, {"SiteNo", Int64.Type}, {"Code", type text}, {"Description", type text}, {"Text", type text}, {"SBSite.CustomerNo", type text}}),
#"Rimosse colonne" = Table.RemoveColumns(#"Modificato tipo",{"Oid", "Code"}),
#"Colonna trasformata tramite Pivot" = Table.Pivot(#"Rimosse colonne", List.Distinct(#"Rimosse colonne"[Description]), "Description", "Text", (x)=>x{0}?),
#"Ordinate righe" = Table.Sort(#"Colonna trasformata tramite Pivot",{{"SiteNo", Order.Descending}})
in
#"Ordinate righe"So need some help nesting your let statement into the total solution you provided. Its putting a squiggly line under the 2nd let so cannot test any further.
let Source = Sql.Database("Root", "Database"), SB_UserDefinedData = Source{[Schema="SB",Item="UserDefinedData"]}[Data], #"Merged Queries" = Table.NestedJoin(SB_UserDefinedData, {"SiteNo"}, SBSite, {"SiteNo"}, "SBSite", JoinKind.LeftOuter), #"Expanded SBSite" = Table.ExpandTableColumn(#"Merged Queries", "SBSite", {"CustomerNo"}, {"SBSite.CustomerNo"}), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"UnitNo", "SeqNo", "Type", "Qty", "OptimisticLockField"}) let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Oid = _t, SiteNo = _t, Code = _t, Description = _t, Text = _t, SBSite.CustomerNo = _t]), # "Modified type" = Table.TransformColumnTypes (Source, {{"Oid", Int64.Type}, {"SiteNo", Int64.Type}, {"Code", type text}, {"Description", type text} , {"Text", type text}, {"SBSite.CustomerNo", type text}}), # "Removed columns" = Table.RemoveColumns (# "Modified type", {"Oid", "Code"}), # "Pivot-transformed column" = Table.Pivot (# "Removed columns", List.Distinct (# "Removed columns" [Description]), "Description", "Text", (x) => x {0}? ), # "Order rows" = Table.Sort (# "Pivot transformed column", {{"SiteNo", Order.Descending}}) in # "Sort lines" in #"Removed Columns"l
Done a little blindly, but if you're lucky it works for you ...
let Source = Sql.Database("Root", "Database"), SB_UserDefinedData = Source{[Schema="SB",Item="UserDefinedData"]}[Data], #"Merged Queries" = Table.NestedJoin(SB_UserDefinedData, {"SiteNo"}, SBSite, {"SiteNo"}, "SBSite", JoinKind.LeftOuter), #"Expanded SBSite" = Table.ExpandTableColumn(#"Merged Queries", "SBSite", {"CustomerNo"}, {"SBSite.CustomerNo"}), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"UnitNo", "SeqNo", "Type", "Qty", "OptimisticLockField"}), # "Modified type" = Table.TransformColumnTypes (#"Removed Columns", {{"Oid", Int64.Type}, {"SiteNo", Int64.Type}, {"Code", type text}, {"Description", type text} , {"Text", type text}, {"SBSite.CustomerNo", type text}}), # "Removed columns" = Table.RemoveColumns (# "Modified type", {"Oid", "Code"}), # "Pivot-transformed column" = Table.Pivot (# "Removed columns", List.Distinct (# "Removed columns" [Description]), "Description", "Text", (x) => x {0}? ), # "Order rows" = Table.Sort (# "Pivot transformed column", {{"SiteNo", Order.Descending}}) in # "Order rows"Your original solutions was done in Italian. So I translated but do not believe it pulled the correct Power Query language. The solution seems to be getting hung up at #"Pivot transformed column". Could I get the English equivalent of this routine just to be sure we are on same page.
