Forum Discussion

E12345's avatar
E12345
Icon for Resolver II rankResolver II
1 year ago
Solved

I need help with using Power Query

Hi,   I have a table which I need to make usable for Power BI. The table is in Excel, and it has double headers. The top column is Year, and underneath each year there are two more columns - Course...
Need Help
  • KarinSzilagyi's avatar
    KarinSzilagyi
    1 year ago

    Hi E12345, you need a combination of transpose/pivot/filldown to achieve what you are trying to do

    Step 1: Your starting point should look like this ideally => years not in the headers, so we can easily use Transpose + Fill Down in the next steps:

    Step 2: Click on Column1 and apply Transform > Transpose. This will give you a new Column1 with one row for each year + a blank row below it. Use Transform > Replace Value if the empty rows (2, 4, 6 and 😎 are not null but blank (= look empty)

     

     

     

     

    Step 3: Select Column1 and apply Transform > Fill > Down to fill the year-values into your null-rows:

     

    Step 4: Select Column1 + Column2 and apply Transform > Unpivot Columns > Unpivot other Columns: 

     

    Step 5: Now Select Column2 (the one with the the values Course and Completions) and apply Transform > Pivot Column. Set the "Values Column" to "Value" (the one with Course A, 34 etc) and open the Advanced options to select "Don't Aggregate"

     

    Step 6: Rename the Columns as needed, delete the extra column "Attribute" and adjust the datatypes as needed:


    I hope that helps! 

tharunkumarRTK's avatar
tharunkumarRTK
Icon for Super User rankSuper User
1 year ago

E12345 

Input data

Output results 

Please find the code

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pY69DsIwDIRfpfLcIXXi/owliAEeIcrYAQmaipb3x+6QvxXJw33n09nOASokaIGHlYlKR4WifOvAhu9nX5hseG+v5XiGdT/pbzu1NzOvtInBxrLssE+GBCjjxxnQeceFLUMpIjxOJVO1Rz6aVdjqyo3lMJRv1Vw2XNnps4Rwp1Qy7vLlWDGB9z8=", 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]),
  #"Kept top rows" = Table.FirstN(Source, 2),
  #"Transposed table" = Table.Transpose(#"Kept top rows"),
  #"Replaced value" = Table.ReplaceValue(#"Transposed table", "", null, Replacer.ReplaceValue, {"Column1"}),
  #"Filled down" = List.Transform(Table.Split(Table.FillDown(#"Replaced value", {"Column1"}),2), each List.Transform(Table.ToColumns(_), each  List.Distinct(_) )),
  #"Removed top rows" = Table.Skip(Source, 2),
  ColumsnAndYear = List.Split(Table.ToColumns(#"Removed top rows"),2),
  #"Converted to table" = Table.FromList(ColumsnAndYear, Splitter.SplitByNothing()),
  #"Added index" = Table.AddIndexColumn(#"Converted to table", "Index", 0, 1, Int64.Type),
  #"Added custom" = Table.AddColumn(#"Added index", "Custom", each let 
columns = #"Filled down"{[Index]}{1},
year = #"Filled down"{[Index]}{0}{0},
result = Table.AddColumn(Table.FromColumns([Column1],columns),  "Year" ,each year)
in 
result),
  Custom = Table.Combine(#"Added custom"[Custom])
in
  Custom

 

 

 

Connect on LinkedIn

 

 

 








Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!