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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors