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.
My data only has two columns. A Product Number and a Customer Number. Each product must have either one or two associated customers and a customer can have as many products as they want. My aim is to split the Customer Number column into Customer 1 and Customer 2 columns (Customer 2 column to be blank if there isn't a second customer). But based on the product Number, so we only ever have one row for each product number.
Is this possible in power query? I've been messign about pivoting and unpivoting but can't find a solution.
Any help would be most appreciated. Thank you.
Go from the first table to the second.
Solved! Go to Solution.
You can use the Table.Group function. The code below assumes that there is one or two customers per Product. If there might be more, the code can be rewritten to handle any number of customers.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAiUdJVMDpVgdJK4hnGsI4hrBuUYgrjEq1wTONQZxTVG5ZnCuCSrXFNUoU6hRsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Num..." = _t, #"Customer Nu..." = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Num...", Int64.Type}, {"Customer Nu...", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Product Num..."}, {
{"Customer 1", each [#"Customer Nu..."]{0}, type nullable text},
{"Customer 2", each try [#"Customer Nu..."]{1} otherwise null, type nullable text}
})
in
#"Grouped Rows"
Thank you so much. I can pretend to understand at first glance but I'll be delving into your suggestion. Thanks again
For arbitary count of columns,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAiUdJVMDpVgdJK4hnGsI4hrBuUYgrjEq1wSFa2lpic5HCBiD1Juics3gXBNUrimqXaZQu2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Number" = _t, #"Customer Number" = _t]),
#"Grouped by Prod Num" = Table.Group(Source, "Product Number", {"Prod", each [Customer Number]}),
Columns = {#"Grouped by Prod Num"[Product Number]} & List.Zip(#"Grouped by Prod Num"[Prod]),
#"To Table" = Table.FromColumns(Columns)
in
#"To Table"
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Thank you very much
You can use the Table.Group function. The code below assumes that there is one or two customers per Product. If there might be more, the code can be rewritten to handle any number of customers.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAiUdJVMDpVgdJK4hnGsI4hrBuUYgrjEq1wTONQZxTVG5ZnCuCSrXFNUoU6hRsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Num..." = _t, #"Customer Nu..." = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Num...", Int64.Type}, {"Customer Nu...", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Product Num..."}, {
{"Customer 1", each [#"Customer Nu..."]{0}, type nullable text},
{"Customer 2", each try [#"Customer Nu..."]{1} otherwise null, type nullable text}
})
in
#"Grouped Rows"
Thank you so much. I can pretend to understand at first glance but I'll be delving into your suggestion. Thanks again
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.