Table is Empty - Power Query

[AllisterB]

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]

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]

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]

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]

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

View attachment 17647

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

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]

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 Order	Make	Model		Sales Order	Make	Model
AAA	Honda	CRV		AAA	Honda	CRV,Accord,Civic
AAA	Honda	Accord		BBB	Toyota	Yaris,Highlander
AAA	Honda	Civic		CCC	Nissan	Altima
BBB	Toyota	Yaris
BBB	Toyota	Highlander
CCC	Nissan	Altima

Sales Order	Make	Model		Query1
				All values are null

 

[AllisterB]

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]

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]

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]

if it works for you, you got an answer

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