Forum Discussion
create a list from a table
hello to all, due to work, I came across a rather complex project:
starting from a table such as that mentioned below which is filled each time with the measurements of doors, the type of opening and the type of jamb, I need to generate a list like the one mentioned below. in the list not appear any omitted value in the table. logically quantities' shown in the table vary, consequently varying the length of the list. The numbers appear in the extreme right side of the list in the end of each measure indicates the total of doors counted for that measure. thanks in advance to those who will give a solution to my problem.
table:
size LH RH J
26 3 2
28 1 2 4.75
28 1 6.75
30
32 1 2
list:
26 LH
26 LH
26 LH
26 RH
26 RH 5
LH 28 4.75
28 RH 4.75
28 RH 4.75
28 LH 6.75 4
32 LH
32 RH
32 RH 3
5 Replies
... and attached is my variant. Not optimized, just for the illustration.
If you have power query, you can try this. Just paste the code in the M edditor.
let
Source = Excel.CurrentWorkbook(){[Name="YourTable"]}[Content],
#"null to cero" = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,{"LH", "RH", "J"}),
#"Unpivot columns" = Table.UnpivotOtherColumns(#"null to cero", {"size"}, "Attribute", "Value"),
#"Cero to null" = Table.ReplaceValue(#"Unpivot columns",0,null,Replacer.ReplaceValue,{"Value"}),
#"Combine Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Cero to null", {{"size", type text}, {"Value", type text}}, "en-US"),{"size", "Attribute", "Value"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"List")
in
#"Combine Columns"Hi,
IMHO, that's not exactly what required. This script generates records like
26 LH 3
instaed of above it shall be 3 records
26 LH
26 LH
26 LH
- You are right, I think I misunderstood the requirement.
Thanks for the clarification!
At first glance i'd use Power Query (aka Get&Transform).
Creaty query from initial table,
Generate one more table which repeats each number from 1 to max possible that numer times (other words, one row for 1, two rows for 2, etc). Most probably could be generated with M script from the list;
Right join source table with the second one for LH;
Same for RH;
Generate required list string for both in new columns;
Remove in each all columns but size and list string;
Append one table to another;
Remove size column
Not sure right now about totals, perhaps one more intermediate table will be required.
Above is just an idea wich could be workable
