cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
garynorcross Frequent Visitor
Frequent Visitor

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:

Capture1.JPG

 

But, I need it to look like this:
Capture2.JPG

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

1 ACCEPTED SOLUTION

Accepted Solutions
LivioLanzo Super Contributor
Super Contributor

Re: Adding an Index Column based on more than one field

Hi @garynorcross

 

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

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

6 REPLIES 6
LivioLanzo Super Contributor
Super Contributor

Re: Adding an Index Column based on more than one field

@garynorcross

 

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

garynorcross Frequent Visitor
Frequent Visitor

Re: Adding an Index Column based on more than one field

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 Super Contributor
Super Contributor

Re: Adding an Index Column based on more than one field

Hi @garynorcross

 

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

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

garynorcross Frequent Visitor
Frequent Visitor

Re: Adding an Index Column based on more than one field

That did it!  Thank you so much LivioLanzo, you're a lifesaver!

garynorcross Frequent Visitor
Frequent Visitor

Re: Adding an Index Column based on more than one field

Hi @LivioLanzo,

 

The user has changed their requirements for this report, so maybe you can help me adjust this query a bit.  They've asked to also include a 'QUANTITY' field.  Here is the updated matrix, how can I bring in the quantity associated with each SEARCHNAME in the example below (ITEMNUMBER has been replaced by SEARCHNAME)?  For instance, for FORMULAID '1002399_V1', the user wants to see Chemical #1 with the associated quantity (92.2) and Chemical #2 with the associated quantity (7.8).  

Capture2.JPG

Highlighted
LivioLanzo Super Contributor
Super Contributor

Re: Adding an Index Column based on more than one field

Hi @garynorcross

 

in the last step of my code you'd need to replace

 

List.Distinct(SortedRows[GROUPNAME])

 

with

 

List.Sum(SortedRows[Quantity])

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 343 members 3,369 guests
Please welcome our newest community members: