# Table is Empty - Power Query



## AllisterB (Jul 5, 2020)

Hi

I want to test in a Power Query if the Excel Table (t_AccrTrialBalance) is empty. If so then display a message and stop the Query otherwise do the transformation.  how do I do this ?

Thank You


My code at present is 
let
    Source = Excel.CurrentWorkbook(){[Name="t_AccrTrialBalance"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type text}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",5),
    #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
    #"Cleaned Text" = Table.TransformColumns(#"Transposed Table",{{"Column1", Text.Clean, type text}}),
    #"Transposed Table1" = Table.Transpose(#"Cleaned Text"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),

in
    #"Promoted Headers"


----------



## sandy666 (Jul 5, 2020)

what do you mean by  *(t_AccrTrialBalance) is empty *?
an empty table has only headers but no rows

btw. use code tags for M , like *[CODE]* _your code here_ *[/CODE]*


----------



## AllisterB (Jul 5, 2020)

sandy666 said:


> what do you mean by  *(t_AccrTrialBalance) is empty *?
> an empty table has only headers but no rows
> 
> btw. use code tags for M , like *[CODE]* _your code here_ *[/CODE]*



HI

I run a macro as below and then ask theUser to past data into it.  If the Query is run before the pasting t is done then the Query errors.

Hope the code below clarifies what I mean by an empty ytable.

Thanks

Allister

 If Not ActiveCell.ListObject Is Nothing Then
    On Error Resume Next
    ActiveCell.ListObject.DataBodyRange.Delete
    On Error GoTo 0

  End If


----------



## sandy666 (Jul 5, 2020)

did you see this?
btw. use code tags for M , like [CODE] your code here [/CODE]






from post #3 it follows that you need vba not M code


----------



## AllisterB (Jul 5, 2020)

sandy666 said:


> did you see this?
> btw. use code tags for M , like [CODE] your code here [/CODE]
> 
> View attachment 17647
> ...




Thanks sandy
The Code I included in post 3 was to show hothe Table became empty and to clarify what i meant by an empty able.

The problem I have is when a PQ runs - hence I need some M Code to correct the power Query.

i do appreciate your interest and wil try and put code in [/CODE] in thefuture

Thank you

Allister


----------



## sandy666 (Jul 5, 2020)

in short: Power Query doesn't support vba (imho fortunately)

so with M you can try Table.Profile to load your table or, if table is blank, show error or defined info, like
Sales OrderMakeModelSales OrderMakeModelAAAHondaCRVAAAHondaCRV,Accord,CivicAAAHondaAccordBBBToyotaYaris,HighlanderAAAHondaCivicCCCNissanAltimaBBBToyotaYarisBBBToyotaHighlanderCCCNissanAltima
Sales OrderMakeModelQuery1All values are null


----------



## AllisterB (Jul 5, 2020)

Hi Sandy

the VBA runs prior to the Query so the fact tat M doesn't support VBA shouldn't be a problem.

Can you supply the M Code to do the

 IF table is Blank then  Output a message
      else perform the transformation steps


Thanks

Allister


----------



## sandy666 (Jul 5, 2020)

sure,
`if Table.Profile(Table1)[Count] = Table.Profile(Table1)[NullCount] then "All values are null" else Table1`
where Table1 is yours table then you need adapt your M to the last name=Table1


----------



## AllisterB (Jul 5, 2020)

Hi Sandy Like this ?

```
let
    Source = Excel.CurrentWorkbook(){[Name="t_AccredoTrialBalance"]}[Content],
    #"Check If table Empty" = if Table.Profile("t_AccredoTrialBalance")[Count] = Table.Profile("t_AccredoTrialBalance")[NullCount] then "All values are null" else,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type text}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",5),
    #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
    #"Cleaned Text" = Table.TransformColumns(#"Transposed Table",{{"Column1", Text.Clean, type text}}),
    #"Transposed Table1" = Table.Transpose(#"Cleaned Text"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"AccountNumber", type number}, {"Account Name", type text}, {"Account Class", type text}, {"Account Type", type text}, {"Inactive", type logical}, {"Year Balance", type number}, {"LY Year Balance", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"AccountNumber", "Account Number"}, {"Year Balance", "TY Balance"}, {"LY Year Balance", "LY Balance"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Number.ToText([Account Number], "0000.000")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Account Number"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Account Number"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"Account Number", "Account Name", "Account Class", "Account Type", "Inactive", "TY Balance", "LY Balance"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns", "Account Number", "Account Number - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Account Number - Copy", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Account Number - Copy.1", "Account Number - Copy.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Account Number - Copy.1", Int64.Type}, {"Account Number - Copy.2", Int64.Type}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type2",{{"Account Number - Copy.1", "Base"}, {"Account Number - Copy.2", "Subcode"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns2",{"Account Number", "Base", "Subcode", "Account Name", "Account Class", "Account Type", "Inactive", "TY Balance", "LY Balance"}),
    #"Added Custom1" = Table.AddColumn(#"Reordered Columns1", "Custom", each Number.ToText([Subcode],"000")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Subcode"}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Removed Columns1",{"Account Number", "Base", "Custom", "Account Name", "Account Class", "Account Type", "Inactive", "TY Balance", "LY Balance"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Reordered Columns2",{{"Account Number", type text}, {"Base", type text}, {"Custom", type text}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Changed Type3",{{"TY Balance", "This Year"}, {"LY Balance", "Last Year"}}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Renamed Columns3",1)
 
in
    #"Removed Bottom Rows"
```
]


----------



## sandy666 (Jul 5, 2020)

if it works for you, you got an answer

I have no source data so I can't test it


----------



## AllisterB (Jul 5, 2020)

Hi

I want to test in a Power Query if the Excel Table (t_AccrTrialBalance) is empty. If so then display a message and stop the Query otherwise do the transformation.  how do I do this ?

Thank You


My code at present is 
let
    Source = Excel.CurrentWorkbook(){[Name="t_AccrTrialBalance"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type text}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",5),
    #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
    #"Cleaned Text" = Table.TransformColumns(#"Transposed Table",{{"Column1", Text.Clean, type text}}),
    #"Transposed Table1" = Table.Transpose(#"Cleaned Text"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),

in
    #"Promoted Headers"


----------



## AllisterB (Jul 5, 2020)

Thank You Sandy


----------



## sandy666 (Jul 5, 2020)

You are welcome


----------



## sandy666 (Jul 5, 2020)

it should work like this


----------



## AllisterB (Jul 5, 2020)

sandy666 said:


> it should work like this



Mine didnt quite work like that- can you sahre the M code  for eth Query with me Thank You


----------



## sandy666 (Jul 5, 2020)

```
// Warning
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Warning = if Table.Profile(Source)[Count] = Table.Profile(Source)[NullCount] then "All values are null" else Base
in
    Warning

// Base
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Sales Order", "Make"}, {{"Count", each _, type table}}),
    Model = Table.AddColumn(Group, "Model", each List.Distinct([Count][Model])),
    ExtractModel = Table.TransformColumns(Model, {"Model", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    ExtractModel
```
two queries: Warning and Base. Warning is an "error" query, Base is your transformed query or whatever you want to do there.
from Warning you should call Base
after all you can load Base into the sheet


----------



## sandy666 (Jul 6, 2020)

*correction:*
 after all you can load Base into the sheet 
after all you can load Warning into the sheet


----------

