Forum Discussion

blazer12219's avatar
blazer12219
Frequent Visitor
4 years ago
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...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    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
Anonymous's avatar
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"

 

 

 

blazer12219's avatar
blazer12219
Frequent Visitor
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

     

    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"