Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
CBVC-HB
Frequent Visitor

Need help transforming a spreadsheet how I want using power query so I can automate it

Hi Guys,

 

I have a csv in the below format (just a small excerpt, actually goes up to 30000 for 2nd row, after which it repeats with 36 on the first row and 5000 to 30000 on 2nd row):

 24        
 5000  8000  10000  
CAP CodeFinanceServiceTotalFinanceServiceTotalFinanceServiceTotal
A123456789
B111213141516171819
C212223242526272829

 

I want to put it into this format:

 

CAP CodeTermMileageFinanceServiceTotal
A245000123
A248000456
A2410000789
B245000111213
B248000141516
B2410000171819
C245000212223
C248000242526
C2410000272829

 

Having trouble doing this, it's feels like a transpose but with extra steps. Hope someone can point me in the right direction.

3 REPLIES 3
CBVC-HB
Frequent Visitor

Is there any way of doing this through the GUI? I was working on it earlier and was able to change it to:

 

CAP CodeTermMileageFinanceServiceTotalTermMileageFinanceServiceTotalTermMileageFinanceServiceTotal
A2450001232480004562410000789
B2450001112132480001415162410000171819
C2450002122232480002425262410000272829

 

So it's like I need column 7-11 to move onto a new row while keeping it's cap code on the first column, and doing it every 5 columns until the end.

slorin
Super User
Super User

Hi

Another solution 

 

let
Source = YourSource,
Transpose = Table.Transpose(Source),
FillDown = Table.FillDown(Transpose,{"Column1", "Column2"}),
Headers = Table.PromoteHeaders(FillDown, [PromoteAllScalars=true]),
ColumnNames = Table.RenameColumns(Headers,{{"Column1", "Term"}, {"Column2", "Mileage"}, {"CAP Code", "Code"}}),
UnPivot = Table.UnpivotOtherColumns(ColumnNames, {"Term", "Mileage", "Code"}, "CAP Code", "Value"),
Pivot = Table.Pivot(UnPivot, List.Distinct(UnPivot[#"Code"]), "Code", "Value")
in
Pivot

Stéphane 

wdx223_Daniel
Super User
Super User

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZA/C4MwEEe/SsjskDurtqMNdC60mziIOghFoUg/f+9FCnbp0OEX8ue9XC5N453PvB5scD/TZhtahBB2B8fvpYT9GifWVxeXYbSNyzR3c8/sNj5fU5rdl7V7/H1GgZqy9GDJLbRSWEpLxQstp0SeIUEFVoAFWsAFXhAEQzYlcnG6HUXzz18piqIoiqKoKe0b", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t]),
    Custom1 = let a=Table.ToColumns(Table.Skip(Source,2)),b=List.Split(List.Skip(a),3) in Table.FillDown(Table.Combine(List.Transform(List.Positions(b),each Table.PromoteHeaders(Table.FromColumns({a{0},{"Term",Source[Column2]{0}},{"Mileage",Record.ToList(Source{1}){_*3+1}}}&b{_})))),{"Term","Mileage"})
in
    Custom1

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors