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
utgqw
Frequent Visitor

Transform data like two row with main title and subtitle ?

can i tranform Left side to Right Side like this img, thank 

https://ibb.co/NxCPNyg

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

Hello @utgqw 

 

 

here another approach. It uses List.Generate to create a new column that identifies the main groups. In the Group-function the first row is deleted and a new column with the main-cat is added

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgQCJR0lQwMDpVidaKXE4pQ0INcYGw/CMYFwnIAAyDOC6itOBEvCjClPLQLyTJH1gTixAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cat = _t, Column1 = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Cat", type text}, {"Column1", Int64.Type}}),
    GenerateGroupColumns = List.Generate
    (
        ()=> [RunningTotal = ChangedType[Column1]{0}, Counter=0, Group=1],
        each [Counter]<= Table.RowCount(ChangedType)-1,
        (rec)=>
        [
            RunningTotal = if rec[RunningTotal] - ChangedType[Column1]{rec[Counter]+1}< 0 then ChangedType[Column1]{rec[Counter]+1} else rec[RunningTotal] - ChangedType[Column1]{rec[Counter]+1},
            Counter = rec[Counter]+1,
            Group = if rec[RunningTotal] - ChangedType[Column1]{rec[Counter]+1}< 0 then rec[Group]+1 else rec[Group]
        ],
        each [Group]
    ),
    CreateTable = Table.FromColumns(Table.ToColumns(ChangedType)&{GenerateGroupColumns}, Table.ColumnNames(ChangedType)&{"Group"}),
    GroupedRows = Table.Group
    (
        CreateTable, 
        {"Group"}, 
        {
            {
                "AllRows", 
                (tbl)=> Table.AddColumn(Table.Skip(tbl,1), "Main", each tbl[Cat]{0})
            }
        }
    ),
    RemovedOtherColumns = Table.SelectColumns(GroupedRows,{"AllRows"}),
    ExpandedAllRows = Table.ExpandTableColumn(RemovedOtherColumns, "AllRows", {"Cat", "Column1", "Group", "Main"}, {"Cat", "Column1", "Group", "Main"})
in
    ExpandedAllRows

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy 

View solution in original post

12 REPLIES 12
ziying35
Impactful Individual
Impactful Individual

Hi, @utgqw 

You can try my code, which currently has a data processing capacity of about 12,000 lines.

// output
let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("bc2xDoIwFIXhV2nuzGApAWVTVl2qm3G4loY0kN6EW+JgeHd1qy3z+XL++xs0vcQZn3ZiaOFojGWm2VmGAjpafIC2UuVaJPDkRis0mjFyUm2za0DfR26fMwphsiy+TnQ4/MWl3HyNSKN2Gbn8JnReJLZscqsJ+9Spus7cjZbZ+SGlh2p9fAA=", BinaryEncoding.Base64),Compression.Deflate))),
    fx = (lsts)=> List.Transform(List.Skip(lsts), each {lsts{0}{0}}&_),
    rows = Table.ToRows(Source)&{{null, null}},
    acc = List.Accumulate(
             rows, 
             {null, 0, {}, {}}, //{sub_total, running_total, list_sub_tbls, result}
             (s,c)=>
                if s{1}+c{1}=2*s{0} then {null, 0, {}, s{3}&{ Table.FromRows(fx(s{2}&{c}), {"Main", "Sub", "Count"}) }} 
                else {if s{1}=0 or s{0}=null then c{1} else s{0}, s{1}+c{1}, s{2}&{c}, s{3}}
          ),
    result = Table.Combine(acc{3})
in
    result

23.png

 

sry i'm newbie where i paste the code?

 

is it working with data like

HeadA

AAAA1

AAAA2

AAAA3

HeadB

BBBB1

BBBB2

HeadC

CCCC1

CCCC2

CCCC3

CCC4    

 

(sub catalogy not equal) ? thank you

 

Jimmy801
Community Champion
Community Champion

Hello @utgqw 

 

 

here another approach. It uses List.Generate to create a new column that identifies the main groups. In the Group-function the first row is deleted and a new column with the main-cat is added

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgQCJR0lQwMDpVidaKXE4pQ0INcYGw/CMYFwnIAAyDOC6itOBEvCjClPLQLyTJH1gTixAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cat = _t, Column1 = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Cat", type text}, {"Column1", Int64.Type}}),
    GenerateGroupColumns = List.Generate
    (
        ()=> [RunningTotal = ChangedType[Column1]{0}, Counter=0, Group=1],
        each [Counter]<= Table.RowCount(ChangedType)-1,
        (rec)=>
        [
            RunningTotal = if rec[RunningTotal] - ChangedType[Column1]{rec[Counter]+1}< 0 then ChangedType[Column1]{rec[Counter]+1} else rec[RunningTotal] - ChangedType[Column1]{rec[Counter]+1},
            Counter = rec[Counter]+1,
            Group = if rec[RunningTotal] - ChangedType[Column1]{rec[Counter]+1}< 0 then rec[Group]+1 else rec[Group]
        ],
        each [Group]
    ),
    CreateTable = Table.FromColumns(Table.ToColumns(ChangedType)&{GenerateGroupColumns}, Table.ColumnNames(ChangedType)&{"Group"}),
    GroupedRows = Table.Group
    (
        CreateTable, 
        {"Group"}, 
        {
            {
                "AllRows", 
                (tbl)=> Table.AddColumn(Table.Skip(tbl,1), "Main", each tbl[Cat]{0})
            }
        }
    ),
    RemovedOtherColumns = Table.SelectColumns(GroupedRows,{"AllRows"}),
    ExpandedAllRows = Table.ExpandTableColumn(RemovedOtherColumns, "AllRows", {"Cat", "Column1", "Group", "Main"}, {"Cat", "Column1", "Group", "Main"})
in
    ExpandedAllRows

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy 

Really nice but it hard for me to understand and apply for work ... thankyou 

Jimmy801
Community Champion
Community Champion

Hello @utgqw 

 

I totally agree. The thing is... easy transformation can be made by using the gui. More complex ones you have to write your code on you own and therefore also not easy to apply. What you can try with my code is to replace my datasource steps with your query. Connect them by changing the step-names in my code and maybe also changing column names in my code.

 

Hope this helps a little bit

 

Jimmy

ziying35
Impactful Individual
Impactful Individual

@utgqw 

To create a new blank query in Power Query, copy and paste the code into the advanced editor of the query.

 

It doesn't matter if you can't distinguish between the main categories, as long as each main category in your data has a data summation for its subcategories, as in the example file you provided.

 

Does your new example only have one column of data?  You try to simulate the data as close to the real data as possible, so that we can give you code suggestions to better fit your real data.

utgqw
Frequent Visitor

this is sample file @Fowmy  thankyou

 

https://gofile.io/d/9CZBJ8

@utgqw 

 

How does Power Query know what your main categories are?

Fowmy_0-1601907334318.png

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

utgqw
Frequent Visitor

Nobody Know maybe data are copy paste  from pivot table to normal range

@Fowmy - maybe using the count column...

take first row as a main category, and the all rows below, which will have total of the first row 😄

(eg. Accessories = 432, then take rows step by step till you reach 432, and so on)

its the only way based on this sample

ziying35
Impactful Individual
Impactful Individual

@utgqw 

Yes, the fastest way to get a solution is to upload a desensitized file that is close to the actual data.

Fowmy
Super User
Super User

@utgqw 

Yes, that will be possible, but as per the sample you have, the Title has two categories AAAA and BBBB with subcategories starting the 1st Letter. Is it the same structure as your real dataset? you can post a sample of the actual dataset after removing any confidential information.


How to paste sample data with your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn   

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors