Grouping and summarizing into rows with column data

[blazer12219]

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 requested help.  Have data output from query on top and desired output below so you can see what trying to do.  I believe it requires grouping, transposing, and such but just cannot get correct sequence down.

Power Query Output
Oid	SiteNo	Code	Description	Text	SBSite.CustomerNo
139	1296	DIV	DIVISION	JACKSONVILLE	SAMPLE-A
133	322	DIV	DIVISION	JACKSONVILLE	SAMPLE-A
132	19	DIV	DIVISION	NEWPORT	SAMPLE-B
136	19	STAT	BILLING STATUS	ACTIVE	SAMPLE-B
137	19	OFF	OFF CONTRACT DATE	04/15/22	SAMPLE-B
138	19	RTRN	RETURN CONTRACT DATE	09/01/22	SAMPLE-B
Desired Output
SiteNo	Division	Billing Status	Off Contract Date	Return Contract Date	SBSite.CustomerNo
1296	JACKSONVILLE	NULL	NULL	NULL	SAMPLE-A
322	JACKSONVILLE	NULL	NULL	NULL	SAMPLE-A
19	NEWPORT	ACTIVE	04/12/22	09/01/22	SAMPLE-B

[ronrsnfld · Accepted solution]

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

[Anonymous]

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"

[blazer12219]

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

[Anonymous]

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" 

[blazer12219]

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.

[Anonymous]

here it is, although I fear that the problem is not the language used to label the steps performed.
try this: copy and paste into a new blank query (overwrite everything there) the following code; in the first step you have to replace "YOURTABNAME ..." with the name of the table / query you want to work on 

let
    Source = #"YOURTABLENAME",
     #"Changed 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(#"Changed Type",{"Oid", "Code"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Description]), "Description", "Text", (x)=>x{0}?)
in
    #"Pivoted Column"