Format vertical data to table in power query

[Anonymous]

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.

[v-yangliu-msft · Accepted solution]

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