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
Hi Jacek,
I'm not clear on what result you actually want. Is the result table in your image what you want? Or is this what you end up with when trying to create the desired result in PQ?
Regards
Phil
hi Phil!
thank you!
Exactly, i want to have Result table using PQ but i am having issues how can i merge Sourcetable and NumbeOfInstace table together to have repeating rows based on MaxInstance column...
Best,
Jacek
Hi Jacek,
The final table is on a new sheet 'Final Query'.
Regards
Phil
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
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.
Catalin you are awesome!!!
Thank you very much love your solution,
It is working,
Best,
Jacek
