Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone - I've been searching for an answer to do this using Power M but I can't seem to find the correct coding.
I'd like to group them by Fund then have each row with new columns.
The issue is I don't know how many donors each fund will have so it would have to count the rows first.
Raw Data:
Fund | DonorID | DonorName | DonorCity | DonorYear |
12330 | 56789 | Sally Smith | Anywhere | 2002 |
12330 | 12345 | Joe Warren | Disneyland | 2015 |
12330 | 99000 | Daffy Duck | Orlando | 2013 |
14558 | 67589 | Bugs Bunny | LA | 2006 |
33566 | 76532 | Ronald McDonald | Nashville | 2010 |
33566 | 76532 | Ronald McDonald | Nashville | 2010 |
33556 | 44532 | Mickey Mouse | Savannah | 2017 |
33556 | 78456 | George McSauce | Washington | 2005 |
Result:
Fund | DonorID | DonorName | DonorCity | DonorYear | DonorID1 | DonorName1 | DonorCity1 | DonorYear1 | DonorID2 | DonorName2 | DonorCity2 | DonorYear2 | DonorID3 | DonorName3 | DonorCtiy3 | DonorYear3 |
12330 | 56789 | Sally Smith | Anywhere | 2002 | 12345 | Joe Warren | Disneyland | 2015 | 99000 | Daffy Duck | Orlando | 2013 | ||||
14558 | 67589 | Bugs Bunny | LA | 2006 | ||||||||||||
33566 | 76532 | Ronald McDonald | Nashville | 2010 | 76532 | Ronald McDonald | Nashville | 2010 | 76532 | Ronald McDonald | Nashville | 2010 | 44532 | Mickey Mouse | Savannah | 2017 |
I've tried many things using Index, AddingRank, Group then Table.Transpose, Pivot, Unpivot but I don't think I have the correct fields where they should be.
Any help would be greatly appreciated!
Thanks,
Carol Grant
Middlebury College
Solved! Go to Solution.
You sound like you were heading in the right direction Carol. The trick is to add an index column during your grouping to get the number of columns. After expanding, then unpivot/merge columns and repivot. Below is a sample presuming that your data is in an internal excel table.
let
Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Fund"}, {{"AllRows", each
Table.AddIndexColumn(_, "Index", 0, 1)
, type table}}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"DonorID", "DonorName", "DonorCity", "DonorYear", "Index"}, {"DonorID", "DonorName", "DonorCity", "DonorYear", "Index"}),
IndexToText = Table.TransformColumns(#"Expanded AllRows",{{"Index", each if _ = 0 then "" else Text.From(_), type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(IndexToText, {"Fund", "Index"}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Index", type text}}, "en-US"),{"Attribute", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Min)
in
#"Pivoted Column"
You sound like you were heading in the right direction Carol. The trick is to add an index column during your grouping to get the number of columns. After expanding, then unpivot/merge columns and repivot. Below is a sample presuming that your data is in an internal excel table.
let
Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Fund"}, {{"AllRows", each
Table.AddIndexColumn(_, "Index", 0, 1)
, type table}}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"DonorID", "DonorName", "DonorCity", "DonorYear", "Index"}, {"DonorID", "DonorName", "DonorCity", "DonorYear", "Index"}),
IndexToText = Table.TransformColumns(#"Expanded AllRows",{{"Index", each if _ = 0 then "" else Text.From(_), type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(IndexToText, {"Fund", "Index"}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Index", type text}}, "en-US"),{"Attribute", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Min)
in
#"Pivoted Column"
Hi @clgrantmidd ,
This is technically achievable and reasonably simple, but I have to ask - do you really need it? What you are trying to achieve is quite bad design, please make sure you know what you do. 😀
This is the main table query (Source = the table in your original post):
let
Source = ...
#"Grouped Rows" = Table.Group(Source, {"Fund"}, {{"Data", each fTranspose(_, {"DonorID", "DonorName", "DonorCity", "DonorYear"})}}),
NewHeaders = List.Distinct(List.Combine(Table.AddColumn(#"Grouped Rows", "Columns", each [Data]{1})[Columns])),
ExtractTables = Table.AddColumn(#"Grouped Rows", "Table", each [Data]{0}),
ExtractValues = Table.ExpandTableColumn(ExtractTables, "Table", NewHeaders),
#"Removed Other Columns" = Table.SelectColumns(ExtractValues,List.Combine({{"Fund"}, NewHeaders}))
in
#"Removed Other Columns"
In the Table.Group you need to pass a list of the fields you are trying to roll, if the sample table is what you need it will work as it is.
This is the function fTranspose called in the main query, create a blank query, rename and copy the content from below:
(pTable as table, pHeaders as list)=>
let
Source = pTable,
NewHeaders = pHeaders,
CutTable = Table.SelectColumns(Source,NewHeaders),
Accumulated = List.Accumulate( Table.ToRows(CutTable), {{}, {}, 1}, (seed, add)=> {seed{0} & add, seed{1} & List.Transform(NewHeaders, each _ & "_" & Number.ToText(seed{2})), seed{2}+1}),
Table = Table.FromRows({Accumulated{0}}, Accumulated{1}),
TableType = Value.Type(Table),
Output = {Table, Accumulated{1}}
in
Output
The function may look complex, but all what it does is stacking the headers together and then it returns a table and column definitions to automate the ExpandTableColumn in the main query.
Kind regards,
JB
JB- thank you for this, you saved me so much work!
I do have a few questions while I'm trying to get the code to work if you have the time.
In the ftranspose function you didn't say what I needed to put in for values (if any) but it does prompt me for some when I go to save the query. I did figure out ptable should equal the query/table I am using so I put in #"mytable" but I am not sure what to do for the NewHeaders. I tried putting in this
{"DonorID", "DonorName", "DonorCity", "DonorYear"}
but that didn't seem to work.
Thanks,
Carol
hi @clgrantmidd
This is a version which combines both parts into one query:
(myTable as table, pDriverColumn as text, pHeaders as list)=>
let
mfTranspose = (pTable as table, pHeaders as list)=>
let
Source = pTable,
NewHeaders = pHeaders,
CutTable = Table.SelectColumns(Source,NewHeaders),
Accumulated = List.Accumulate( Table.ToRows(CutTable), {{}, {}, 1}, (seed, add)=> {seed{0} & add, seed{1} & List.Transform(NewHeaders, each _ & "_" & Number.ToText(seed{2})), seed{2}+1}),
Table = Table.FromRows({Accumulated{0}}, Accumulated{1}),
TableType = Value.Type(Table),
Output = {Table, Accumulated{1}}
in
Output,
Source = myTable,
mDriverColumn = pDriverColumn,
mHeaders = pHeaders,
#"Grouped Rows" = Table.Group(Source, {mDriverColumn}, {{"Data", each mfTranspose(_, mHeaders)}}),
NewHeaders = List.Distinct(List.Combine(Table.AddColumn(#"Grouped Rows", "Columns", each [Data]{1})[Columns])),
ExtractTables = Table.AddColumn(#"Grouped Rows", "Table", each [Data]{0}),
ExtractValues = Table.ExpandTableColumn(ExtractTables, "Table", NewHeaders),
#"Removed Other Columns" = Table.SelectColumns(ExtractValues,List.Combine({{mDriverColumn}, NewHeaders}))
in
#"Removed Other Columns"
Please create a Blank Query, rename it to clgrantmidd and in the advanced editor replace the content with the above code.
The function takes three arguments: the original table, the column you use as a driver (in your case "Fund") and the list of columns that need to be "transposed" (basically all other columns in your example). You can use it in this manner:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZA7a8MwFIX/ivCcQbZe9phgCJS6hXrIEDIIR7FF1CuQoxT9+15LQ8nc6dxzOR/3cT5XdcMYrXaVkKrtUEftXCLjt30s6PaQfhYTDJYNpU112f0RqFygvnlDTjoEA2h6u4JJTsM1I7V4QbqO0k17fbsl0sfpjuYzbHFf8qzkuRAtNqQSealDnFdyiAAJzfu+bCNzlDEhJTaUFKxB/fKg3ZUMU58L7HzodXla58oRNf0nJjaM84INdrqbRAYfV5Of99QAeimIekFUy7MejQ+zwUmjjtMGnXCQhfnhodyFL7v8Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Fund = _t, DonorID = _t, DonorName = _t, DonorCity = _t, DonorYear = _t]),
Result = clgrantmidd(Source, "Fund", {"DonorID", "DonorName", "DonorCity", "DonorYear"})
in
Result
Kind regards,
JB
Thank you both for your time and energy on this for me.
I finally got back to it and it and I tried out mcybulski's script also. That one seemed to work and it looked a little easier for me to understand. Thank you mcybulski!
JB- thank you for your input also, I will be referring to your script when I get more advanced.
-Carol