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,
I'm hoping someone can help me get addresses spread over a few rows into one row. The first table below is my input table and the second table is what I'd like to have as the output.
Any thoughts?
Solved! Go to Solution.
Thanks everyone. I figured it out myself.
let Source = vendors, Partition = Table.Group(Source, {"Account No"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Supplier Address", "Index"}, {"Partition.Supplier Address", "Partition.Index"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Partition", {{"Partition.Index", type text}}, "en-AU"), List.Distinct(Table.TransformColumnTypes(#"Expanded Partition", {{"Partition.Index", type text}}, "en-AU")[Partition.Index]), "Partition.Index", "Partition.Supplier Address") in #"Pivoted Column"
Cheers
Thanks everyone. I figured it out myself.
let Source = vendors, Partition = Table.Group(Source, {"Account No"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Supplier Address", "Index"}, {"Partition.Supplier Address", "Partition.Index"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Partition", {{"Partition.Index", type text}}, "en-AU"), List.Distinct(Table.TransformColumnTypes(#"Expanded Partition", {{"Partition.Index", type text}}, "en-AU")[Partition.Index]), "Partition.Index", "Partition.Supplier Address") in #"Pivoted Column"
Cheers
very nice
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi I tried this on my data which is similar but it puts all of the address information against the first row and creates over 8,000 columns. Any tips on what might be wrong. I copied your code
The second step should be a Fill Down.... revised code:
let Source = vendors,
FillDown = Table.FillDown(Source, {"Account No"}), Partition = Table.Group(FillDown, {"Account No"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Supplier Address", "Index"}, {"Partition.Supplier Address", "Partition.Index"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Partition", {{"Partition.Index", type text}}, "en-AU"), List.Distinct(Table.TransformColumnTypes(#"Expanded Partition", {{"Partition.Index", type text}}, "en-AU")[Partition.Index]), "Partition.Index", "Partition.Supplier Address") in #"Pivoted Column"
That's great it works fine now
not sure but try this, select first column, right click and choose "pivot others"
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |