Search code examples
powerbipowerquerypowerbi-desktop

Table Formatting in Power BI

I've uploaded data from ODBC to Power BI, but the table is not properly formatted for analysis. The picture below shows the current table and the way I'd like it to be formatted.

enter image description here

The code in advanced editor is as follows:

let
Source = Odbc.DataSource("driver={SQL Server};server=cmisql3\SQLEXPRESS;app=Microsoft® Windows® Operating System;database=QTD;dsn=dBASE Files", [HierarchicalNavigation=true]),
QTD_Database = Source{[Name="QTD",Kind="Database"]}[Data],
dbo_Schema = QTD_Database{[Name="dbo",Kind="Schema"]}[Data],
Results_Table = dbo_Schema{[Name="Results",Kind="Table"]}[Data]
in Results_Table

I tried the solution from here

In powerquery, you can paste this into home...advanced editor... using your first row in place of mine:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index1" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index1", "Custom", each if Text.Contains([Column1],"24 Hr") then [Index] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Removed Columns1" = Table.RemoveColumns(#"Filled Down",{"Index"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Column1", "Custom"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Column1]), "Column1", "Value", List.Sum)
in  #"Pivoted Column"

I changed the column order so TestName was the first column but, I had an error that said Column1 was not found. I tried renaming TestName to Column1 in the code and the same error came up. I also tried renaming Column1 to TestName in the code, but got the message that TestName could not be found.

Solution

  • Steps:

    • Select columns: ID, TestName, TestSeq, Pass/Fail then Unpivot other columns
    • Select column: TestName, then Pivot Column
    let
  Source = Odbc.DataSource("driver={SQL Server};server=cmisql3\SQLEXPRESS;app=Microsoft® Windows® Operating System;database=QTD;dsn=dBASE Files", [HierarchicalNavigation=true]),
  QTD_Database = Source{[Name="QTD",Kind="Database"]}[Data],
  dbo_Schema = QTD_Database{[Name="dbo",Kind="Schema"]}[Data],
  Results_Table = dbo_Schema{[Name="Results",Kind="Table"]}[Data],
  #"Unpivoted other columns" = Table.UnpivotOtherColumns(Results_Table , {"ID", "TestName", "TestSeq", "Pass/Fail"}, "Value", "Value#"),
  #"Pivoted column" = Table.Pivot(Table.TransformColumnTypes(#"Unpivoted other columns", {{"TestName", type text}}), List.Distinct(Table.TransformColumnTypes(#"Unpivoted other columns", {{"TestName", type text}})[TestName]), "TestName", "Value#")
in
  #"Pivoted column"