Forum Discussion
Format vertical data to table in power query
Hi,
I have 2000+ rows i am trying to format. See screenshot for the given (left) and desired(right). if the blank columns can be renamed that would be awesome but understand if not since there is no current data.
Hi Anonymous ,
Here are the steps you can follow：
1. Append two tables in power query.
And click Add Column - Index Column - From1. Add a column of Index.
2. Create calculated column.
IN = if( CONTAINSSTRING('Append1'[Name],"INVOICE"),'Append1'[Name])Column = SWITCH(TRUE(), CONTAINSSTRING('Append1'[Name],"DBA"),'Append1'[Name], CONTAINSSTRING('Append1'[Name],"TAP"),'Append1'[Name])Column 2 = SWITCH(TRUE(), CONTAINSSTRING('Append1'[Name],"KOH"),'Append1'[Name], CONTAINSSTRING('Append1'[Name],"14838"),'Append1'[Name])Column 3 = var _number={"1","2","3","4","5","6""7","8","9"} return IF( LEFT('Append1'[Name]) in _number &&CONTAINSSTRING('Append1'[Name],"-"),'Append1'[Name])Column 4 = var _number={"1","2","3","4","5","6""7","8","9"} return IF( LEFT('Append1'[Name],1) in _number&& RIGHT('Append1'[Name],1) in _number&& CONTAINSSTRING('Append1'[Name],"-")=FALSE()&&'Append1'[Name]<>"14838", 'Append1'[Name])Column 5 = if( CONTAINSSTRING('Append1'[Name],"USD"),'Append1'[Name])Column 6 = if( CONTAINSSTRING('Append1'[Name],"2021"),'Append1'[Name])3. Create calculated table.
All table: all = SUMMARIZE('Append1','Append1'[Column],'Append1'[Column 2],'Append1'[Column 3],'Append1'[Column 4],Append1[Column 5],Append1[Column 6],'Append1'[Index])True Table:
True = var _table=SUMMARIZE('all','all'[Column], "1",IF('all'[Column]="DBA Co.", CALCULATE(MAX('all'[Column 2]),FILTER(ALL('all'),'all'[Index]=MAXX(FILTER( ALL('all'),'all'[Column 2]<>BLANK()),[Index]))), CALCULATE(MAX('all'[Column 2]),FILTER(ALL('all'),'all'[Index]=MinX(FILTER( ALL('all'),'all'[Column 2]<>BLANK()),[Index])))), "2",IF('all'[Column]="DBA Co.", CALCULATE(MAX('all'[Column 3]),FILTER(ALL('all'),'all'[Index]=MAXX(FILTER( ALL('all'),'all'[Column 3]<>BLANK()),[Index]))), CALCULATE(MAX('all'[Column 3]),FILTER(ALL('all'),'all'[Index]=MinX(FILTER( ALL('all'),'all'[Column 3]<>BLANK()),[Index])))), "3",IF('all'[Column]="DBA Co.", CALCULATE(MAX('all'[Column 4]),FILTER(ALL('all'),'all'[Index]=MAXX(FILTER( ALL('all'),'all'[Column 4]<>BLANK()),[Index]))), CALCULATE(MAX('all'[Column 4]),FILTER(ALL('all'),'all'[Index]=MinX(FILTER( ALL('all'),'all'[Column 4]<>BLANK()),[Index])))), "4",IF('all'[Column]="DBA Co.", CALCULATE(MAX('all'[Column 5]),FILTER(ALL('all'),'all'[Index]=MAXX(FILTER( ALL('all'),'all'[Column 5]<>BLANK()),[Index]))), CALCULATE(MAX('all'[Column 5]),FILTER(ALL('all'),'all'[Index]=MinX(FILTER( ALL('all'),'all'[Column 5]<>BLANK()),[Index])))), "5",IF('all'[Column]="DBA Co.", CALCULATE(MAX('all'[Column 6]),FILTER(ALL('all'),'all'[Index]=MAXX(FILTER( ALL('all'),'all'[Column 6]<>BLANK()),[Index]))), CALCULATE(MAX('all'[Column 6]),FILTER(ALL('all'),'all'[Index]=MinX(FILTER( ALL('all'),'all'[Column 6]<>BLANK()),[Index])))) ) return FILTER(_table, [Column] <>BLANK())4. Result:
Please click here for the pbix file
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
1 Reply
Hi Anonymous ,
Here are the steps you can follow：
1. Append two tables in power query.
And click Add Column - Index Column - From1. Add a column of Index.
2. Create calculated column.
IN = if( CONTAINSSTRING('Append1'[Name],"INVOICE"),'Append1'[Name])Column = SWITCH(TRUE(), CONTAINSSTRING('Append1'[Name],"DBA"),'Append1'[Name], CONTAINSSTRING('Append1'[Name],"TAP"),'Append1'[Name])Column 2 = SWITCH(TRUE(), CONTAINSSTRING('Append1'[Name],"KOH"),'Append1'[Name], CONTAINSSTRING('Append1'[Name],"14838"),'Append1'[Name])Column 3 = var _number={"1","2","3","4","5","6""7","8","9"} return IF( LEFT('Append1'[Name]) in _number &&CONTAINSSTRING('Append1'[Name],"-"),'Append1'[Name])Column 4 = var _number={"1","2","3","4","5","6""7","8","9"} return IF( LEFT('Append1'[Name],1) in _number&& RIGHT('Append1'[Name],1) in _number&& CONTAINSSTRING('Append1'[Name],"-")=FALSE()&&'Append1'[Name]<>"14838", 'Append1'[Name])Column 5 = if( CONTAINSSTRING('Append1'[Name],"USD"),'Append1'[Name])Column 6 = if( CONTAINSSTRING('Append1'[Name],"2021"),'Append1'[Name])3. Create calculated table.
All table: all = SUMMARIZE('Append1','Append1'[Column],'Append1'[Column 2],'Append1'[Column 3],'Append1'[Column 4],Append1[Column 5],Append1[Column 6],'Append1'[Index])True Table:
True = var _table=SUMMARIZE('all','all'[Column], "1",IF('all'[Column]="DBA Co.", CALCULATE(MAX('all'[Column 2]),FILTER(ALL('all'),'all'[Index]=MAXX(FILTER( ALL('all'),'all'[Column 2]<>BLANK()),[Index]))), CALCULATE(MAX('all'[Column 2]),FILTER(ALL('all'),'all'[Index]=MinX(FILTER( ALL('all'),'all'[Column 2]<>BLANK()),[Index])))), "2",IF('all'[Column]="DBA Co.", CALCULATE(MAX('all'[Column 3]),FILTER(ALL('all'),'all'[Index]=MAXX(FILTER( ALL('all'),'all'[Column 3]<>BLANK()),[Index]))), CALCULATE(MAX('all'[Column 3]),FILTER(ALL('all'),'all'[Index]=MinX(FILTER( ALL('all'),'all'[Column 3]<>BLANK()),[Index])))), "3",IF('all'[Column]="DBA Co.", CALCULATE(MAX('all'[Column 4]),FILTER(ALL('all'),'all'[Index]=MAXX(FILTER( ALL('all'),'all'[Column 4]<>BLANK()),[Index]))), CALCULATE(MAX('all'[Column 4]),FILTER(ALL('all'),'all'[Index]=MinX(FILTER( ALL('all'),'all'[Column 4]<>BLANK()),[Index])))), "4",IF('all'[Column]="DBA Co.", CALCULATE(MAX('all'[Column 5]),FILTER(ALL('all'),'all'[Index]=MAXX(FILTER( ALL('all'),'all'[Column 5]<>BLANK()),[Index]))), CALCULATE(MAX('all'[Column 5]),FILTER(ALL('all'),'all'[Index]=MinX(FILTER( ALL('all'),'all'[Column 5]<>BLANK()),[Index])))), "5",IF('all'[Column]="DBA Co.", CALCULATE(MAX('all'[Column 6]),FILTER(ALL('all'),'all'[Index]=MAXX(FILTER( ALL('all'),'all'[Column 6]<>BLANK()),[Index]))), CALCULATE(MAX('all'[Column 6]),FILTER(ALL('all'),'all'[Index]=MinX(FILTER( ALL('all'),'all'[Column 6]<>BLANK()),[Index])))) ) return FILTER(_table, [Column] <>BLANK())4. Result:
Please click here for the pbix file
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly