Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
@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
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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"
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
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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).
Hi @Anonymous
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!
That did it! Thank you so much LivioLanzo, you're a lifesaver!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |