

February 1, 2019

Hi,
i have source table like here:
and the numberOfInstances like here:
The number of instance can be from 1 to n, for example we have only 2. So resultTable should be changed accordingly.
The last table result table is looking like:
So as you can see there is Instance number for each TopologyTier in order way.
We have just doubled rows because we have instanceNumber from 1 to 2.
How can i build the Result table using PQ?
Cany anybody help?
Please,
Jacek


February 1, 2019

Hi Phil,
thank you very much. This is not working unfortunately.
I changed maxInstance from 2 to 3 and now i should get:
and in your resuleTable i got:
so as you can see this is not copying and increasing automatically rows...
If i will add 4 as MaxInstance i will have rows from 1 to 4 times 3 and so on...
Can you please help?
Jacek


November 8, 2013

Basically, you want to repeat all rows from Source table, based on MaxInstance count?
Try this query for t_SourceTable:
Source = Excel.CurrentWorkbook(){[Name="t_SourceTable"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Environment", type text}, {"Infrastructure", type text}, {"Region", type text}, {"Topology", type text}, {"Tier", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Topology", "Tier"}, t_Instances, {"Topology", "Tier"}, "t_Instances", JoinKind.LeftOuter),
#"Expanded t_Instances" = Table.ExpandTableColumn(#"Merged Queries", "t_Instances", {"MinInstance", "MaxInstance"}, {"MinInstance", "MaxInstance"}),
#"Added Custom" = Table.AddColumn(#"Expanded t_Instances", "Custom", each {[MinInstance]..[MaxInstance]}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
#"Expanded Custom"
The key here is to create a list, based on your conditions: {[MinInstance]..[MaxInstance]}. Then just expand the list into rows, this will create duplicate rows for each item in list.
Load results to sheet, hope this is what you're after.

Answers Post
1 Guest(s)
