Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Adding an Index Column based on more than one field

Hi all,

 

I am trying to create an index column based on 3 fields: 

FORMULAID

ITEMNUMBER

GROUPNAME

 

Currently, the table looks like this:

 

But, I need it to look like this:


Basically, I need the table to pivot out and (by FORMULAID) show each ITEMNUMBER that corresponds with a GROUPNAME, and list out multiple GROUPNAMEs as #1, #2, etc.  How can I achieve this through Power Query?

 

Thanks,

Gary

Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    Hi Anonymous

     

    you would need to remove the first step in my query 

     

    then in my second step (which is referincing the table named Source):

     

    ChangedType = Table.TransformColumnTypes(Source,{{"FORMULAID", type text}, {"ITEMNUMBER", Int64.Type}, {"GROUPNAME", type text}}),

     

    you would need to replace source with the name of latest step which as far as i see is:

    #"Sorted Rows1"

     

    and then you can copy paste the rest of my code at the bottom. But you need to be careful if you have extra columns within your query than those you've shown in your earlier print screen

     

6 Replies

  • LivioLanzo's avatar
    LivioLanzo
    Icon for Solution Sage rankSolution Sage
    7 years ago

    Anonymous

     

    you can do it like this:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc87CoAwDAbgu3R2sGp9jD4nbyAOFRwEraK9PyYpYq0dMuQjJH+GgZUsYByq3rdjVvpiY2AwIlRaLo9WIDGqVC8kUL2c5tVQDa2gmVPv6rX0O9ZAm9mrEHI3BmLhQx569fcHafQ93aLFTsQOMbEDkQh3IWnq1cyrv59ICyvSeAM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FORMULAID = _t, ITEMNUMBER = _t, GROUPNAME = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"FORMULAID", type text}, {"ITEMNUMBER", Int64.Type}, {"GROUPNAME", type text}}),
    
    A = Table.Group(
                ChangedType, 
                {"FORMULAID", "GROUPNAME"}, 
                {
                    "T", 
                    each Table.AddIndexColumn(_, "Index", 1, 1), 
                    type table
                }),
    RemovedOtherColumns = Table.SelectColumns(A,{"T"}),
    ExpandedT = Table.ExpandTableColumn(RemovedOtherColumns, "T", {"FORMULAID", "ITEMNUMBER", "GROUPNAME", "Index"}, {"FORMULAID", "ITEMNUMBER", "GROUPNAME", "Index"}),
    MergedColumns = Table.CombineColumns(Table.TransformColumnTypes(ExpandedT, {{"Index", type text}, {"ITEMNUMBER", type text}, {"FORMULAID", type text}}, "en-US"),{"GROUPNAME", "Index"},Combiner.CombineTextByDelimiter(" #", QuoteStyle.None),"GROUPNAME"),
    SortedRows = Table.Sort(MergedColumns,{{"GROUPNAME", Order.Ascending}}),
    PivotedColumn = Table.Pivot(SortedRows, List.Distinct(SortedRows[GROUPNAME]), "GROUPNAME", "ITEMNUMBER")
    
in
    PivotedColumn
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you for your response, where would that code fit into the already existing code that I have?  See below.

      let
      Source = Sql.Database("crcgls-sql01.database.windows.net", "CRCGLS-DB01"),
      dbo_PmfFormulaLineV2Staging = Source{[Schema="dbo",Item="PmfFormulaLineV2Staging"]}[Data],
      #"Sorted Rows" = Table.Sort(dbo_PmfFormulaLineV2Staging,{{"RECID", Order.Descending}}),
      #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"FORMULAID", "QUANTITY", "ITEMNUMBER", "LINENUMBER", "DATAAREAID"}),
      #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"FORMULAID", "LINENUMBER", "ITEMNUMBER", "QUANTITY", "DATAAREAID"}),
      #"Merged Queries" = Table.NestedJoin(#"Reordered Columns",{"ITEMNUMBER"},EcoResReleasedProductV2Staging,{"ITEMNUMBER"},"EcoResReleasedProductV2Staging",JoinKind.LeftOuter),
      #"Expanded EcoResReleasedProductV2Staging" = Table.ExpandTableColumn(#"Merged Queries", "EcoResReleasedProductV2Staging", {"PRODUCTGROUPID"}, {"PRODUCTGROUPID"}),
      #"Merged Queries1" = Table.NestedJoin(#"Expanded EcoResReleasedProductV2Staging",{"PRODUCTGROUPID"},InventProductGroupStaging,{"GROUPID"},"InventProductGroupStaging",JoinKind.LeftOuter),
      #"Expanded InventProductGroupStaging" = Table.ExpandTableColumn(#"Merged Queries1", "InventProductGroupStaging", {"GROUPNAME"}, {"GROUPNAME"}),
      #"Capitalized Each Word" = Table.TransformColumns(#"Expanded InventProductGroupStaging",{{"GROUPNAME", Text.Proper, type text}}),
      #"Filtered Rows" = Table.SelectRows(#"Capitalized Each Word", each ([PRODUCTGROUPID] = "RM010" or [PRODUCTGROUPID] = "RM020" or [PRODUCTGROUPID] = "RM030" or [PRODUCTGROUPID] = "RM040" or [PRODUCTGROUPID] = "RM050" or [PRODUCTGROUPID] = "RM060" or [PRODUCTGROUPID] = "RM090")),
      #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"LINENUMBER", "QUANTITY", "DATAAREAID", "PRODUCTGROUPID"}),
      #"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([GROUPNAME] <> "Chemicals")),
      #"Sorted Rows1" = Table.Sort(#"Filtered Rows1",{{"FORMULAID", Order.Ascending}, {"GROUPNAME", Order.Ascending}})


      in
      #"Sorted Rows1"

      • LivioLanzo's avatar
        LivioLanzo
        Icon for Solution Sage rankSolution Sage
        7 years ago

        Hi Anonymous

         

        you would need to remove the first step in my query 

         

        then in my second step (which is referincing the table named Source):

         

        ChangedType = Table.TransformColumnTypes(Source,{{"FORMULAID", type text}, {"ITEMNUMBER", Int64.Type}, {"GROUPNAME", type text}}),

         

        you would need to replace source with the name of latest step which as far as i see is:

        #"Sorted Rows1"

         

        and then you can copy paste the rest of my code at the bottom. But you need to be careful if you have extra columns within your query than those you've shown in your earlier print screen