Log In
Register
Home
February 1, 2019
Offline
Hi Guys,
i have table like here:
Topology|Tier|TotalServers
| Topology1 | Tier1 | 4 |
| Topology1 | Tier2 | 2 |
| Topology2 | Tier3 | 3 |
as you can see there is TotalServers for each topology and tier.
And now the expected result is:
Topology|TierServer|Number
| Topology1 | Tier1 | 1 |
| Topology1 | Tier1 | 2 |
| Topology1 | Tier1 | 3 |
| Topology1 | Tier1 | 4 |
| Topology1 | Tier2 | 1 |
| Topology1 | Tier2 | 2 |
| Topology2 | Tier3 | 1 |
| Topology2 | Tier3 | 2 |
| Topology2 | Tier3 | 3 |
so generally i want to break TotalServers into Servernumber when TotalServers is max and min =1. Something like list.generate from 1 to n where n is the TotalServers.
Can anybody help?
Best,
Jacek
See Answer
VIP
Trusted Members
December 7, 2016
Offline
Hello,
A similar question was asked in this post, for me the suggested approach works just fine, check if it works for you.
Answers Post
February 1, 2019
Offline
Hi,
thank you.
Hmm i stucked a little.
I tried example from link above and i finished with:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Building", type text}, {"Device", type text}, {"Ports", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Repeat("z",[Ports])),
#"Split Column by Position" = Table.ExpandListColumn(Table.TransformColumns(#"Added Custom", {{"Custom", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Custom", type text}})
in
#"Changed Type1"
What i am missing?
Best,
Jacek
Trusted Members
Moderators
November 1, 2018
Offline
You could use something like this I think:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Building", type text}, {"Device", type text}, {"Ports", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Counter", each {1..[Ports]}),
#"Expanded Counter" = Table.ExpandListColumn(#"Added Custom", "Counter")
in
#"Expanded Counter"
The following users say thank you to Velouria for this useful post:
Anders SehlstedtVIP
Trusted Members
December 7, 2016
Offline
Hello,
Velouria's code gives a better result. But to answer your question, you got the expected result for that text split code, so nothing missed on your part. You got a new row for each z in the custom column, it is however not counting as what Velouria's code does.
February 1, 2019
Offline
Hi,
thank you very much.
It is working.
Ok so the link above is not doing the right solution?
One question:
{1..[Ports]} what these ".." means within list?
Best,
Jacek
October 5, 2010
Offline
hi Jaryszek,
The .. in {1..[Ports]} means include all numbers from 1 to [Ports]
Further reading Power Query Lists • My Online Training Hub
regards
Phil
1 Guest(s)
