none
Transform Data with PowerQuery RRS feed

  • Question

  • Hi all,

    can someone please give me an approch how to transform the following kind of export with powerquery to an usefull one like this:

    Date-Critera1-Criteria2-Location-TeamNumber-KPI

    This is the one i have to transform:


    Criteria 1
    Criteria 2
    Location
    Team Number
    Date KPI
    01.10.2015 271 217
    02.10.2015 170 144  
    03.10.2015 19 14
    04.10.2015 92 70  
    05.10.2015 196 140
    06.10.2015 166 129  
    07.10.2015 158 123
    08.10.2015 144 110  
    09.10.2015 195 150
    10.10.2015 94 85  
    11.10.2015
    12.10.2015 313 260  
    13.10.2015 292 239
    14.10.2015      
    15.10.2015
    16.10.2015      
    17.10.2015
    18.10.2015      
    19.10.2015

    Thanks a lot.

    Chris



     



    Tuesday, October 20, 2015 1:37 PM

Answers

  • try this:

    let
        Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
        RemoveC4 = Table.RemoveColumns(Source,{"Column4"}),
        FilterOut = Table.SelectRows(RemoveC4, each [Column1] <> null ),
        Ind = Table.AddIndexColumn(FilterOut, "Index", -4, 1),
        Crit1 = Table.AddColumn(Ind, "Cr1", each try(if [Column1] ="Date" then Ind[Column1]{[Index]} else if Text.Start([Column1],3)="Sum" then "Del" else null) otherwise null),
        Crit2 = Table.AddColumn(Crit1 , "Cr2", each try(if [Column1] ="Date" then Ind[Column1]{[Index]+1} else if Text.Start([Column1],3)="Sum" then "Del" else null) otherwise null),
        Crit3 = Table.AddColumn(Crit2 , "Cr3", each try(if [Column1] ="Date" then Ind[Column1]{[Index]+2} else if Text.Start([Column1],3)="Sum" then "Del" else null) otherwise null),
        Crit4 = Table.AddColumn(Crit3 , "Cr4", each try(if [Column1] ="Date" then Ind[Column1]{[Index]+3} else if Text.Start([Column1],3)="Sum" then "Del" else null) otherwise null),
        FeelIt = Table.FillDown(Crit4,{"Cr1", "Cr2", "Cr3", "Cr4"}),
        FilterIt = Table.SelectRows(FeelIt, each ([Cr1] <> "Del" and [Cr1] <> null) and ([Column1] <> "Date")),
        RenameIt = Table.RenameColumns(FilterIt,{{"Column1", "Date"}, {"Column2", "numerator"}, {"Column3", "denominator"}, {"Cr3", "Location"}, {"Cr4", "Team"}, {"Cr1", "Column2"}, {"Cr2", "Column3"}}),
        RemoveInd = Table.RemoveColumns(RenameIt,{"Index"}),
        MoveIt = Table.ReorderColumns(RemoveInd,{"Date", "Column2", "Column3", "Location", "Team", "numerator", "denominator"})
    in
        MoveIt

    May be, not so good and clever solution, but I think it should works...

    Note please, that I used named range "Input" as source to PQ. And also, in your sample data, sometimes there are "Date" in first column (before dates block), sometimes "Sum" instead (not in the totals rows, but in header rows). Also sometmes "Summe", not "Sum".

    • Marked as answer by xXHartiganXx Thursday, October 22, 2015 7:07 PM
    Thursday, October 22, 2015 4:15 PM

All replies

  • What is that extra column to the right of KPI, another KPI?
    Tuesday, October 20, 2015 2:49 PM
  • BTW, that is ug, ug, ug, ugly source data. The only thing I can suggest would be to use a text parsing program to get it into a decent format. Outside of that, you could import it into Power Query (it will likely come in as a single column with Date and KPI and extra weird column to the right all collapsed and then use Transpose and then wipe out the extra "Date" columns, split the remaining "Date" column on "Space". Rinse and repeat query for remaining Date columns then merge them all together.

    Is this a single file or do you have a bunch of files that you need to process like this?

    Good luck.


    Tuesday, October 20, 2015 3:00 PM
  • Hi Greg,

    thanks for your reply.

    You`re right: the extra column is another KPI. 

    It is a single file, but every week i get a new one in many versions.

    For now, i use vba to transform this source data into a format i can work with. But im very interested in the possibilities of powerquery. I use it for some other reports and it works very well.

    I would like to import this source data into my datamodel without transforming it via VBA or other parsing pogramms. But after reading your reply i think this doesn`t work.

    Thank You.

    Chris

    Wednesday, October 21, 2015 8:25 AM
  • The screenshots you've provided don't seem to fit your description. Could you upload a file instead?

    Imke Feldmann TheBIccountant.com

    Wednesday, October 21, 2015 3:01 PM
    Moderator
  • Hi Imke,

    this is a anonymous version of the file:

    http://1drv.ms/1M7UD6y

    best regards

    Thursday, October 22, 2015 12:07 PM
  • H xXHartiganXx,

    Looks like nothing special... but what output format you want to get? Can you show an example in the same file? source kind of different from your first message

    Thursday, October 22, 2015 1:35 PM
  • Hi Maxim,

    i uploaded another version of the excel file with the wanted output format:

    http://1drv.ms/1jD0LgX

    It is the same source data like i posted in my first message. But i omitted the last column (because its not relevant to me).

    Maybe i have to say, that the values "Critera1", "Criteria2" etc. vary and not always are the same. So in Colum2 (in my wanted Output Data) doesnt only exist "Criteria1" after transforming.

    I hope i could explain it good enough. My english is not so good. Sorry.


    Thursday, October 22, 2015 2:01 PM
  • try this:

    let
        Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
        RemoveC4 = Table.RemoveColumns(Source,{"Column4"}),
        FilterOut = Table.SelectRows(RemoveC4, each [Column1] <> null ),
        Ind = Table.AddIndexColumn(FilterOut, "Index", -4, 1),
        Crit1 = Table.AddColumn(Ind, "Cr1", each try(if [Column1] ="Date" then Ind[Column1]{[Index]} else if Text.Start([Column1],3)="Sum" then "Del" else null) otherwise null),
        Crit2 = Table.AddColumn(Crit1 , "Cr2", each try(if [Column1] ="Date" then Ind[Column1]{[Index]+1} else if Text.Start([Column1],3)="Sum" then "Del" else null) otherwise null),
        Crit3 = Table.AddColumn(Crit2 , "Cr3", each try(if [Column1] ="Date" then Ind[Column1]{[Index]+2} else if Text.Start([Column1],3)="Sum" then "Del" else null) otherwise null),
        Crit4 = Table.AddColumn(Crit3 , "Cr4", each try(if [Column1] ="Date" then Ind[Column1]{[Index]+3} else if Text.Start([Column1],3)="Sum" then "Del" else null) otherwise null),
        FeelIt = Table.FillDown(Crit4,{"Cr1", "Cr2", "Cr3", "Cr4"}),
        FilterIt = Table.SelectRows(FeelIt, each ([Cr1] <> "Del" and [Cr1] <> null) and ([Column1] <> "Date")),
        RenameIt = Table.RenameColumns(FilterIt,{{"Column1", "Date"}, {"Column2", "numerator"}, {"Column3", "denominator"}, {"Cr3", "Location"}, {"Cr4", "Team"}, {"Cr1", "Column2"}, {"Cr2", "Column3"}}),
        RemoveInd = Table.RemoveColumns(RenameIt,{"Index"}),
        MoveIt = Table.ReorderColumns(RemoveInd,{"Date", "Column2", "Column3", "Location", "Team", "numerator", "denominator"})
    in
        MoveIt

    May be, not so good and clever solution, but I think it should works...

    Note please, that I used named range "Input" as source to PQ. And also, in your sample data, sometimes there are "Date" in first column (before dates block), sometimes "Sum" instead (not in the totals rows, but in header rows). Also sometmes "Summe", not "Sum".

    • Marked as answer by xXHartiganXx Thursday, October 22, 2015 7:07 PM
    Thursday, October 22, 2015 4:15 PM
  • Hi Maxim,

    thanks a lot. I will give it a try. This is code i can learn from.

    Have a nice day.

    Edit: It works very fine! ;)

    Friday, October 23, 2015 5:32 AM