Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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