Data in column group format

Power Query M Code Showcase
1

Hey all,

I have an excel file that has data in a slightly annoying format, I’ve taken the transformation so far but I can’t quite get over the line from where I’ve ended up.

Data starts off like this below. One date column with the US state name in row 1 of the data and then Kia/Hyundai, All and Percent all relate to each US state. The columns are basically in Groups of 3. I don’t need the Percent column so I’ve removed that in my M Code.

I just need to get this in a nice tabular format with the US State repeating down the rows and Kia/Hyundai and All following suit. Then stacking the other column States, Kia/Hyundai and All underneath them.

So I should have 4 Columns, Date, State, Kia/Hyundai and All.

image
image1439×404 22.4 KB

This is where I have gotten to with my transformation

image
image1134×393 15.5 KB

PBIX -
Kia Kyundai Theft v1.0.00.pbix (154.6 KB)

2

Hi @DavieJoe,

Can you share the source xlsx as well?

Also a quick mock up of the result is appreciated :wink:
Just headers and one or two lines of data will do.

Thanks!

2 Likes
3

I’m in the office today, when I get home this evening I’ll upload all this for you as I can’t access it just now.

Thanks as always Melissa :grinning::+1:t2:

4

So the data is set up like below

image
image887×138 4.44 KB

The Percent column can disappear

The result would be as per below;

image

Motherboard VICE News Kia Hyundai Theft Data.xlsx (255.1 KB)

Thanks in advance.

5

Hi @DavieJoe,

Sorry for the delayed response, life got a bit busy…
Decided to free form code it, can you see if that works for you.

let
    Source = Data,
    ColsToDelete = List.Select(
        Table.ColumnNames(Source), 
        each List.NonNullCount( List.FindText(Table.Column(Source, _), "Percent")) <> 0
            or List.IsEmpty( List.RemoveNulls(Table.Column(Source, _)))
    ),
    Result = Table.RemoveColumns(Source, ColsToDelete),
    RemoveBlankRows = Table.SelectRows( Result, each 
        not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
    ),
    Transform = Table.Combine( 
        List.Transform( List.Split( List.Skip( Table.ToColumns( RemoveBlankRows ), 1), 2), each 
        Table.FillDown( 
            Table.FromColumns( {Table.Column(RemoveBlankRows, "Column1")} & {{_{0}{0}}} & _), 
            {"Column2"}
        ))),
    SetName = Transform{1}[Column3],
    NoBlankDates = Table.SelectRows(Transform, each ([Column1] <> null)),
    RenamedCols = Table.RenameColumns( NoBlankDates,
        {
            {"Column1", "Date"}, 
            {"Column2", "Location"}, 
            {"Column3", SetName}, 
            {"Column4", "All"}
        }
    )
in
    RenamedCols

I hope this is helpful

2 Likes
6

@Melissa No worries at all, I’ve had a hectic week too so no worries. Really appreciate your time on this, will do my best to check it out this weekend.

DJ

7

This works beautifully @Melissa Thank you for taking the time to solve this for me after your busy week. I had a crazy week too so for you to resolve this for me is much appreciated.

1 Like
8

Always happy to help @DavieJoe :+1:
Cheers!

1 Like