LinkBack URL
About LinkBacks
Hello
in appended query, looking to delete null cells ( don't delete entire rows) in query and shift cells up
Hello
in appended query, looking to delete null cells ( don't delete entire rows) in query and shift cells up
Last edited by alansidman; 06-15-2022 at 09:44 AM.
Please try
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Grouped = Table.Group(Source, {"DATA"}, {{"T", each Table.ToList(_)}}), Combine = Table.FromColumns(Grouped[T],Grouped[DATA]) in Combine
Thanks working but I am looking for steps after I append the tables, don't start from beginning and use main table
because the data will be different not same, u will separate it by column name
looking for custom situation
try this
let Source = Table.Combine({A, B, C}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Attribute"}, {{"Data", each _, type table [Attribute=text, Value=text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Value", "Index"}, {"Custom.Value", "Custom.Index"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Attribute"), #"Transposed Table" = Table.Transpose(#"Pivoted Column") in #"Transposed Table"
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
can't post the formula according to forum issues.
See the attached file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Please try
let Source = Excel.CurrentWorkbook(){[Name="Append1"]}[Content], Com = Table.FromColumns(List.Transform(Table.ToColumns(Source),List.RemoveNulls), Table.ColumnNames(Source)) in Com
Formullas
unicateFormula:
=IFERROR(INDEX($A$1:$A$100,MATCH(TRUE,INDEX((COUNTIF($B1:B1,$A$1:$A$100)=0),0),0)),"")
valueFormula:=IFERROR(INDEX($A$1:$A$100,AGGREGATE(15,6,ROW($1:$100)/($A$1:$A$100=C$1),ROWS($2:2))),"")
Thanks all
Bo_Ry #6
May I know why power query is no read the code if its in upper case?
COM = TABLE.FROMCOLUMNS(LIST.TRANSFORM(TABLE.TOCOLUMNS(SOURCE),LIST.REMOVENULLS), TABLE.COLUMNNAMES(SOURCE))
Power Query is case sensitive. All function keys must be according to the case. To see exact case, in a new query in the function line, type #share
Also read the following: https://www.sqlbi.com/articles/lette...ula%20language.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks