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 OidSiteNoCodeDescriptionTextSBSite.CustomerNo1391296DIVDIVISIONJACKSONVILLESAMPLE-A133322DIVDIVISIONJACKSONVILLESAMPLE-A13219DIVDIVISIONNEWPORTSAMPLE-B13619STATBILLING STATUSACTIVESAMPLE-B13719OFFOFF CONTRACT DATE04/15/22SAMPLE-B13819RTRNRETURN CONTRACT DATE09/01/22SAMPLE-B Desired Output SiteNoDivisionBilling StatusOff Contract DateReturn Contract DateSBSite.CustomerNo1296JACKSONVILLENULLNULLNULLSAMPLE-A322JACKSONVILLENULLNULLNULLSAMPLE-A19NEWPORTACTIVE04/12/2209/01/22SAMPLE-B

You should be able to do this all from the UI:Remove the unneeded columns "Code" and "OidPivot the "Description" ColumnValues Column = TextAdvanced Options => Don't AggregateThen reorder your columns as you wish

Grouping and summarizing into rows with column data

6 Replies

Anonymous

Not applicable

4 years ago

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

Frequent Visitor

4 years ago

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"

Anonymous

Not applicable

4 years ago

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"

ronrsnfld
Super User
4 years ago
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

Forum Discussion

Grouping and summarizing into rows with column data

6 Replies

Related content

Grouping rows by Column and then updating Column

Create columns group

Parameter in summarize

Grouping Columns

Summarize Multiple Columns

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