Log In
Register
Home
February 1, 2019
Offline
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
See Answer
October 5, 2010
Offline
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
February 1, 2019
Offline
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
October 5, 2010
Offline
Hi Jacek,
The final table is on a new sheet 'Final Query'.
Regards
Phil
February 1, 2019
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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)
