Forum Discussion

sentsara's avatar
sentsara
Helper II
8 years ago
Solved

Excel first row removing

Hi,

I have excel as Data Source for my powerBI Report. Below is the output of excel:

 

I need to remove the first row from PowerBI and always it should start from the second row (as an header value).

can you help any option available in powerBI for this?

        $       1,300.00 $      -   $      -    
Purchase DateTypeTradeGBMilesUnLockedSell Date Purchase price  Sold Price  Profit  Net Amount  Profit(%) 
5/26/2018iphone 6Y64 GB YES  $                 147.00    
Need Help
  • Michal_cwiok's avatar
    Michal_cwiok
    8 years ago

    When you import data from Excel using "Get Data" module, you can select which worksheet to work with:

     

     

    This is where you select it. Do you see the same interface?

4 Replies

  • Michal_cwiok's avatar
    Michal_cwiok
    Resolver II
    8 years ago

    Yes, it is feasible with the Power BI Inferface. 

     

    First please click on Remove top rows and select 1.

     

     

    Next Use first row as headers:

     

    The effect:

     

    And full M code:

    let
    Source = Excel.Workbook(File.Contents("C:\PowerBI.xlsx"), null, true),
    Arkusz1_Sheet = Source{[Item="Arkusz1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Arkusz1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Purchase Date", type text}, {"Type", type text}, {"Trade", type text}, {"GB", type text}, {"Miles", type text}, {"UnLocked", type text}, {"Sell Date", type text}, {" Purchase price ", type text}, {" Sold Price ", type text}, {" Profit ", type text}, {" Net Amount ", type text}, {" Profit(%) ", type text}})
in
    #"Changed Type1"

    Hope this helps!

    Thanks

    • sentsara's avatar
      sentsara
      Helper II
      8 years ago

      Thanks Michal for quick solution..

       

      i have multiple worksheet on the MobileTrade.xlsx.. example below

       

      RFC <sheet1>

      MobileTd <sheet2>

      Bike <sheet3>

       

      I need to load only MobileTd sheet from MobileTrade.xlsx.. how we can do this?

      • Michal_cwiok's avatar
        Michal_cwiok
        Resolver II
        8 years ago

        When you import data from Excel using "Get Data" module, you can select which worksheet to work with:

         

         

        This is where you select it. Do you see the same interface?